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
You need to do one of two things (and both of these assume that Table0
has all instances of num
) -
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
.
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)