1

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

  • 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`. – Rick James May 14 '17 at 23:16
  • 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 . – Garvit Jain May 15 '17 at 4:06
  • Show us the data and the desired result. – Rick James May 15 '17 at 4:07
  • Edited question for the same! – Garvit Jain May 15 '17 at 5:19
  • 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. – Lennart May 15 '17 at 5:40
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)
  • 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 May 21 '17 at 15:33
  • did it using where coalesce(q46,q47) is not null – Garvit Jain May 21 '17 at 15:52
  • 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) – stefan May 21 '17 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.