Full outer join : Mssql to Mysql conversion

Question

I have this query in MSSQL .. how to convert it into MYSQL query..

CREATE  VIEW DATA_VIEW AS SELECT A.*,B.FIELD_VALUE D1_VALUE,C.FIELD_VALUE D2_VALUE,D.FIELD_VALUE 
C_VALUE FROM DB_DATA A
FULL OUTER JOIN  T_CASE_D1 B ON A.FIELD_ID =B.FIELD_ID AND B.CASE_ID=47758 
FULL OUTER JOIN  T_CASE_D2 C ON A.FIELD_ID =C.FIELD_ID AND C.CASE_ID=47758 
FULL OUTER JOIN  T_CASE_QC D ON A.FIELD_ID =D.FIELD_ID AND D.CASE_ID=47758 
WHERE A.FORM_ID=5 AND IS_ACTIVE='Y'

thanks in advance

MySQL doesn't support full outer joins. This needs to be replaced with a `.. left join ... union ... right join ...` combination — a_horse_with_no_name, Jan 12 '15 at 12:25
possible duplicate of [Full Outer Join in MySQL](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) — a_horse_with_no_name, Jan 12 '15 at 12:26
The duplicate shows up as the first hit when searching for `[mysql] full outer join` — a_horse_with_no_name, Jan 12 '15 at 12:27

score 2 · Answer 1 · answered Jan 12 '15 at 12:29

Your where clause is turning the FULL OUTER JOIN into a LEFT JOIN anyway, so you might as well just use that:

CREATE VIEW DATA_VIEW AS 
    SELECT A.*, B.FIELD_VALUE D1_VALUE, C.FIELD_VALUE D2_VALUE, D.FIELD_VALUE AS C_VALUE
    FROM DB_DATA A LEFT JOIN 
         T_CASE_D1 B
         ON A.FIELD_ID = B.FIELD_ID AND B.CASE_ID = 47758 LEFT OUTER JOIN
         T_CASE_D2 C
         ON A.FIELD_ID = C.FIELD_ID AND C.CASE_ID = 47758 LEFT OUTER JOIN
         T_CASE_QC D
         ON A.FIELD_ID  =D.FIELD_ID AND D.CASE_ID = 47758 
    WHERE A.FORM_ID=5 AND IS_ACTIVE = 'Y';

FULL OUTER JOIN is rarely needed in a database with properly maintained foreign key relationships.

Full outer join : Mssql to Mysql conversion

1 Answers1