SELECT FROM with multiple Outer Joins on Subquery

Question

I checked the recommended threads before posting as well as tried rewriting this query a few times, however from my testing on Microsoft Sql Server this query is the most optimized for what I am trying to achieve.

Background: I am converting from Microsoft SQL Server to MySQL. This query originally was optimized on SQL Server, I am trying to rewrite it for MySQL.

SELECT (T1.`ID`, T1.`Col1`, T1.`Col2`,
T2.`ID` as 'Data1', T2.`Col7`, T2.`Col5`,
T2.`Col2` as 'Data2', T2.`Col8`, T2.`Col6`,
T2.`Col9`, T2.`Col10`,
T3.`Col3` as 'Data3',
T4.`Col3` as 'Data4', T4.`Value`) FROM
(
    (SELECT `ID`, `Col1`, `Col2` FROM TABLE1) T1
    FULL OUTER JOIN
    (SELECT `ID`, `Col4`, `Col7`, `Col5`, `Col2`, `Col8`, `Col6`, `Col9`, `Col10` FROM TABLE2) T2 on T2.`Col4` = T1.`ID`
    FULL OUTER JOIN
    (SELECT `Col12`, `Col3` FROM TABLE3) T3 on T3.`Col12` = T2.`ID`
    FULL OUTER JOIN
    (SELECT `Col12`, `Col3`, `Value` FROM TABLE4) T4 on T4.`Col12` = T2.`ID`
);

This query pulls all the associated data that I need. I have implied a lot of the columns (removed some) and tried to make it forward without my table names and column names. But the error I receive is

"SELECT" is not valid at this position for this server version, expecting '(', WITH

MySQ 8.0.21

Do you need to do this: _(SELECT `ID`, `Col1`, `Col2` FROM TABLE1) T1_? Can you say _TABLE1 T1_ and select the columns? — clinomaniac, Feb 09 '21 at 21:22
MySQL does not support `FULL OUTER JOIN`. There are several other questions on Stack Overflow describing workarounds, for example: https://stackoverflow.com/q/4796872/20860 — Bill Karwin, Feb 09 '21 at 21:26
Bill, Thank you. That is the correct answer. I have replaced 'FULL OUTER JOIN' with 'LEFT JOIN' since I want all records from the left table, and any associated data from the right tables. — Jay Mason, Feb 09 '21 at 21:44

SELECT FROM with multiple Outer Joins on Subquery

0 Answers0