sqlmysqljoin

SQL Full Outer Join with Multiple Tables


First I have 4 Tables

Table0, Columns: num, desc
Table1, Columns: num, qty1
Table2, Columns: num, qty2
Table3, Columns: num, qty3
Table4, Columns: num, qty4

(not all num have values in qty1 or qty2 or qty3 or qty4, therefore I need a full join) and my query:

SELECT Table0.num, SUM(Table1.qty1 ), SUM(Table2.qty2 ), SUM(Table3.qty3 ), SUM(Table4.qty4)
FROM Table0
FULL OUTER JOIN Table1 ON Table0.num = Table1.num
FULL OUTER JOIN Table2 ON Table0.num = Table2.num
FULL OUTER JOIN Table3 ON Table0.num = Table3.num
FULL OUTER JOIN Table4 ON Table0.num = Table4.num
GROUP BY Table0.num

Somehow its returning just 1 row of data:

num | qty1 | qty2 | qty3 | qty4 |
---------------------------------
    | 100  | 20   |  77  |  969 |

But I was expecting like the example at

http://www.w3schools.com/sql/sql_join_full.asp

like:

num | qty1 | qty2 | qty3 | qty4 |
---------------------------------
1   |   0  |  2   |  3   |   2  |
2   |   1  |  0   |  0   |   0  |
3   |   7  |  0   |  9   |   0  |
4   |   0  |  0   |  0   |  10  |
5   |   0  |  0   |  7   |   0  |
6   |   8  |  2   |  9   |   3  |
7   |   0  |  1   |  0   |   0  |

(I don't know this solves it) However I got similar to the result the box above by changing all the tables to:

Table1, Columns: num, qty1, qty2, qty3, qty4
Table2, Columns: num, qty2, qty1, qty3, qty4
Table3, Columns: num, qty3, qty1, qty2, qty4 
Table4, Columns: num, qty4, qty1, qty2, qty3 

Solution

  • You need to do one of two things (and both of these assume that Table0 has all instances of num) -

    1. If all rows are already summed for the 'leaf' tables (1 - 4), then a simple LEFT JOIN (with a COALESCE() in the select) will suffice - you don't even need the GROUP BY.

    2. If you need the rows summed, you're going to need to sum them before the join, given that otherwise multiple rows per num in different tables will cause the results to multiply.

    Something like this:

    SELECT Table0.num, COALESCE(Table1.qty, 0), COALESCE(Table2.qty, 0), 
                       COALESCE(Table3.qty, 0), COALESCE(Table4.qty, 0)
    FROM Table0
    LEFT JOIN (SELECT num, SUM(qty1) as qty
               FROM Table1
               GROUP BY num) Table1
    ON Table1.num = Table0.num
    LEFT JOIN (SELECT num, SUM(qty2) as qty
               FROM Table2
               GROUP BY num) Table2
    ON Table2.num = Table0.num
    LEFT JOIN (SELECT num, SUM(qty3) as qty
               FROM Table3
               GROUP BY num) Table3
    ON Table3.num = Table0.num
    LEFT JOIN (SELECT num, SUM(qty4) as qty
               FROM Table4
               GROUP BY num) Table4
    ON Table4.num = Table0.num
    

    (working SQLFiddle example)