Getting null values when pivoting the data based on the join condition-CodePudding

I have one requirement where I have to join two tables based on the id. I have done the fiddle as well for this but there is one column E_CLUBBED_E which is populating as null but ideally, it should match the column ref_ans_value of ques_ans table and give the output but I am getting null.

Reason: I am getting null because ref_ans_id is null for ref_ques_id and I am joining

join ans_ref ar
on ar.ref_ans_id = qa.ref_ans_id

so this might be the reason of getting null values.

But this is the ideal scenario where for ref_ques_id 23 ref_ans_id will be null only and based on this I have to populate ref_ans_value of ques_ans table.

Fiddle : Fiddle

CodePudding user response：

You just need to change your last inner join to left join of table ans_ref -

select  d.e_id,
        max(case qa.ref_ques_id when 3 then ar.code_value end) changes_exist,
        max(case qa.ref_ques_id when 2 then ar.code_value end) E_Clubbed,
        max(case qa.ref_ques_id when 4 then ar.code_value end) E_impacted,
        max(case qa.ref_ques_id when 23 then qa.ref_ans_value end) E_CLUBBED_E
  from      details_1 d
        join
            ques_ans qa
          on d.e_id = qa.e_id
        left join ans_ref ar
          on ar.ref_ans_id = qa.ref_ans_id
 group by d.e_id

Fiddle.