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.
Advertisement
Answer
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;