Join Multiple Derived Tables, Correctly in MySQL?

Question

I'm trying to achieve a full outer join in MySQL

EDIT : As per comments:

Data is

enter image description here

Need data with for question_id in(44,45,46,47,48,49)

Desired Output is :enter image description here 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

• Rick James: If this is a dup, mark it as such -- or delete it. If you don't have enough reputation, we'll do it for you. If you trying to do a FULL OUTER JOIN, it is 'incorrect`.
• Garvit Jain: It is supposed to be merged with the other 9 instead of showing NULL ; I'm a newbie to database ; Yes I'm trying to achieve FULL OUTER JOIN which is not built into MySQL .
• Rick James: Show us the data and the desired result.
• Garvit Jain: Edited question for the same!
• Lennart - Slava Ukraini: So user=0 is the questions and user>0 is the answers, correct? If that is the case you should consider fixing the model, a question and an answer is not the same thing and should not be represented in the same table.

Answer 1

Regarding your FULL OUTER JOIN question (not working in MySQL): you can probably achieve a result similar to that of a FULL OUTER join by coding a UNION of a LEFT and RIGHT join. However, looking at your raw data and your "Desired Output", it may be better to use pivoting.

Consider the following - just for generating a test table that resembles your "data" table (using MySQL version 5.1):

create table user( userid int );
create table question( qid int, answer varchar(128));
insert into user values (0),(4),(5),(6),(7),(8),(9),(10);
insert into question (qid) values (44),(45),(46),(47),(48),(49);
create table qa 
as select * from user, question;
-- Add some dummy answers to the table, leave some gaps.
update qa 
set answer = concat(concat('answer', userid), qid)
where mod( (userid*qid), 2 ) = 0 ;

We get a table that looks like this: (middle section not displayed)

mysql> select * from qa;
+--------+------+------------+
| userid | qid | answer |
+--------+------+------------+
| 0 | 44 | answer044 |
| 0 | 45 | answer045 |
| 0 | 46 | answer046 |
| 0 | 47 | answer047 |
| 0 | 48 | answer048 |
| 0 | 49 | answer049 |
| 4 | 44 | answer444 |
| 4 | 45 | answer445 |
| 4 | 46 | answer446 |
 ...
| 8 | 49 | answer849 |
| 9 | 44 | answer944 |
| 9 | 45 | NULL |
| 9 | 46 | answer946 |
| 9 | 47 | NULL |
| 9 | 48 | answer948 |
| 9 | 49 | NULL |
| 10 | 44 | answer1044 |
| 10 | 45 | answer1045 |
| 10 | 46 | answer1046 |
| 10 | 47 | answer1047 |
| 10 | 48 | answer1048 |
| 10 | 49 | answer1049 |
+--------+------+------------+
48 rows in set (0.00 sec)

If we now "extend" and "pivot" (see http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/ ) and use MySQL's group_concat() function ( see https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html ), we get a result that may be useful for you.

create view qa_extended as
select 
 userid
, case when qid = 44 then answer end as q44 
, case when qid = 45 then answer end as q45 
, case when qid = 46 then answer end as q46 
, case when qid = 47 then answer end as q47 
, case when qid = 48 then answer end as q48
, case when qid = 49 then answer end as q49 
from qa
;
create view qa_pivot as
select 
 userid
, group_concat(q44) as q44
, group_concat(q45) as q45 
, group_concat(q46) as q46 
, group_concat(q47) as q47 
, group_concat(q48) as q48
, group_concat(q49) as q49 
from qa_extended
group by userid
;

Output of select * from qa_pivot;

mysql> select * from qa_pivot;
+--------+------------+------------+------------+------------+------------+------------+
| userid | q44 | q45 | q46 | q47 | q48 | q49 |
+--------+------------+------------+------------+------------+------------+------------+
| 0 | answer044 | answer045 | answer046 | answer047 | answer048 | answer049 |
| 4 | answer444 | answer445 | answer446 | answer447 | answer448 | answer449 |
| 5 | answer544 | NULL | answer546 | NULL | answer548 | NULL |
| 6 | answer644 | answer645 | answer646 | answer647 | answer648 | answer649 |
| 7 | answer744 | NULL | answer746 | NULL | answer748 | NULL |
| 8 | answer844 | answer845 | answer846 | answer847 | answer848 | answer849 |
| 9 | answer944 | NULL | answer946 | NULL | answer948 | NULL |
| 10 | answer1044 | answer1045 | answer1046 | answer1047 | answer1048 | answer1049 |
+--------+------------+------------+------------+------------+------------+------------+
8 rows in set (0.00 sec)

• Garvit Jain: Thanks for the answer it really worked just a slight problem how to remove the nulls records if only q46,q47 are selected? like: sqlfiddle.com/#!9/4e3c7d/3
• Garvit Jain: did it using where coalesce(q46,q47) is not null
• stefan: Thanks for letting us know! If you want to get rid of the rows containing nulls (in your "fiddle" example), try adding a HAVING after GROUP BY. Something like: having q47 is not null and q49 is not null (see sqlfiddle.com/#!9/4e3c7d/24)