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

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

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)