I'm trying to achieve a full outer join
in MySQL
EDIT : As per comments:
Data is
Need data with for question_id in(44,45,46,47,48,49)
Desired Output is :
Query generated using golang:
select @s:=@s+1,q1,q2,q3,q4,q5,q6 from (SELECT * FROM (SELECT * FROM (select ifnull(answer,' ') as 'q1',user as 'u1' from survey_answer where question_id=44) t1 left join (select ifnull(answer,' ') as 'q2',user as 'u2' from survey_answer where question_id=45) t2 on t1.u1 = t2.u2 left join (select ifnull(answer,' ') as 'q3',user as 'u3' from survey_answer where question_id=46) t3 on t2.u2 = t3.u3 left join (select ifnull(answer,' ') as 'q4',user as 'u4' from survey_answer where question_id=47) t4 on t3.u3 = t4.u4 left join (select ifnull(answer,' ') as 'q5',user as 'u5' from survey_answer where question_id=48) t5 on t4.u4 = t5.u5 left join (select ifnull(answer,' ') as 'q6',user as 'u6' from survey_answer where question_id=49) t6 on t5.u5 = t6.u6 ) X UNION (SELECT * FROM (select ifnull(answer,' ') as 'q1',user as 'u1' from survey_answer where question_id=44) t1 right join (select ifnull(answer,' ') as 'q2',user as 'u2' from survey_answer where question_id=45) t2 on t1.u1 = t2.u2 right join (select ifnull(answer,' ') as 'q3',user as 'u3' from survey_answer where question_id=46) t3 on t2.u2 = t3.u3 right join (select ifnull(answer,' ') as 'q4',user as 'u4' from survey_answer where question_id=47) t4 on t3.u3 = t4.u4 right join (select ifnull(answer,' ') as 'q5',user as 'u5' from survey_answer where question_id=48) t5 on t4.u4 = t5.u5 right join (select ifnull(answer,' ') as 'q6',user as 'u6' from survey_answer where question_id=49) t6 on t5.u5 = t6.u6 ) ) ORDERED_ALIAS , (SELECT @s:= -1) AS s ORDER BY u1=0 DESC
FULL OUTER JOIN
, it is 'incorrect`.FULL OUTER JOIN
which is not built into MySQL .