CODEWITHSUNDEEP
sqloracle-databasejoinleft-joinwhere-clause
Geek
Geek

Reputation: 3329

Join tables to display null if right side table doesnt have data

I have a feedback_ques, feedback_ans table as follows:

feedback_ques
 
id  question                created_date    created_by delete_date
1   How was the training    16-SEP-20       900         null
2   facility?               16-SEP-20       900         null    
3   Dept?                   16-SEP-20       902         null
4   Infrastructure          16-SEP-20       900         16-SEP-20

feedback_ans

ques_id member_id   answers created_date    created_by
1       10          good    16-SEP-20       891
2       10          good    16-SEP-20       891
3       10          poor    16-SEP-20       891
4       10          good    16-SEP-20       891

I want to join the tables as follows:

select q.id as id, a.member_id as memberId, q.question as Ques, a.answers as Ans
from feedback_ques q, feedback_ans a
where q.id = a.ques_id(+) 
and a.member_id = 10
and q.DELETE_DATE IS NULL;

This gives me all the fields. I want the query to return null for answers if they arent found in the answers table. For example member_id 20 doesnt have answers, so i would want the table to display null values as below for this query.

select q.id as id, a.member_id as memberId, q.question as Ques, a.answers as Ans
from feedback_ques q, feedback_ans a
where q.id = a.ques_id(+) 
and a.member_id = 20
and q.DELETE_DATE IS NULL;

ID  memberId    Ques                    ans
1   20          How was the training    null
2   20          facility?               null
3   20          Dept?                   null
4   20          Infrastructure          null

Updated: As suggested im using leftOuter join as follows:

select q.id as id, a.member_id as memberId, q.question as Ques, a.answers as Ans
from feedback_ques q
left join feedback_ans a on a.ques_id = q.id and a.member_id = 20
and q.delete_date is null;

But this query is not working when delete_date is !=null. That row is still returned by the query. From above quest 4 should not be returned since delete_date != null. Can you please help.

Upvotes: 0

Views: 52

Answers (1)

GMB
GMB

Reputation: 222722

These old-shool, implicit joins, make it uneasy to express what you want. Here, the condition a.member_id = 20 in the where clause filters out unmatched rows.

This is just one of the reasons while explicit, standard joins should always be used. Consider a left join, where all conditions on the left table are placed in the on clause:

select q.id as id, a.member_id as memberId, q.question as Ques, a.answers as Ans
from feedback_ques q
left join feedback_ans a on a.ques_id = q.id and a.member_id = 20
where q.delete_date is null;

Upvotes: 1

Related Questions