SQL Full Outer Join with Multiple Tables

2
votes

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
3
mysqlsqldatabasejoin
It's quite impressive that it's returning any data at all, given that MySQL doesn't support FULL OUTER JOIN. - eggyal
Would need to show us the rows in these tables to. It would appear Table0.num contains null or whitespace instead of the values you expect. - AaronLS
Table0.num has no null blank values - user1618180
Table1~4.num 's value are equal to values in Table0.num - user1618180
You are clearly not using MySQL if that statement runs without error. - a_horse_with_no_name

3 Answers

2
votes

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)

2
votes

There are no matches between the num columns in each table and thus you are getting the outer records. As when there is no match on the match key, the records are shown with that column as null.

The way you full outer join, Table0.num would need to exist in ALL the other tables. I.e. if num == 1 was only in Table0 and Table1, but not Table2 and 3, then it will not match on all 4 and thus be a null num.

What you probably want is something more like

SELECT Table0.num, 
     (Select SUM(Table1.qty1 ) From Table1 Where Table1.num = Table0.num) as one,
     (Select SUM(Table2.qty1 ) From Table2 Where Table2.num = Table0.num) as two,
     ...
From Table0

My syntax might be a little off and there's probably more efficient ways. But the general idea is you do a subquery for each relation since they are independent.

-2
votes

There is a work-around solution. You can add one additional table to join your tables. For example:

Tablex; columns: tablex_id (PK, AI) (newly created table)

Table0; columns: tablex_id, num, desc (added new field 'tablex_id')

Table1; columns: tablex_id, num, qty1 (added new field 'tablex_id')

Table2; columns: tablex_id, num, qty2 (added new field 'tablex_id')

Table3; columns: tablex_id, num, qty3 (added new field 'tablex_id')

Table4; columns: tablex_id, num, qty4 (added new field 'tablex_id')

Every time you add record to Table0, Table1, Table2, Table3 and Table4, you should add record to Tablex first and get the tablex_id and put it in your table(s) accordingly.

If you want to join them, just do something like this:

select t0.desc, t1.qty, t2.qty, t3.qty, t4.qty
from Tablex tx
left join Table0 t0
  on tx.tablex_id = t0.tablex_id
left join Table0 t1
  on tx.tablex_id = t1.tablex_id
left join Table2 t2
  on tx.tablex_id = t2.tablex_id
left join Table3 t3
  on tx.tablex_id = t3.tablex_id
left join Table4 t4
 on tx.tablex_id = t4.tablex_id