ohmyan ohmyan - 1 year ago 67
SQL Question

Merge tables and keeping all distinct ID values

I have multiple tables to merge and these tables may have different ID values.

For example:

Table 1:

ID Year Month Size1
A 2015 4 10
B 2015 5 20

Table 2:

ID Year Month Size2
A 2015 4 20
C 2015 5 40

Table 3:

ID Year Month Size3
D 2015 6 50
E 2015 7 50

I want the merged table to look like this:

ID Year Month Size1 Size2 Size3
A 2015 4 10 20 NULL
B 2015 5 20 NULL NULL
C 2015 5 NULL 40 NULL
D 2015 6 NULL NULL 50
E 2015 7 NULL NULL 50

I want the output ID column to include all distinct IDs from all the tables.
My guess is that this can be somehow achieved by using Full Outer Join On ID, but I wasn't quite able to produce the desired output format.

Answer Source

Here's another possible query that would give the result you show:

SELECT t.id, t.year, t.month, 
  SUM(size1) AS size1, SUM(size2) AS size2, SUM(size3) AS size3 
  SELECT id, year, month, size1, NULL AS size2, NULL AS size3 FROM t1
  SELECT id, year, month, NULL, size2, NULL FROM t2
  SELECT id, year, month, NULL, NULL, size3 FROM t3
) AS t
GROUP BY t.id, t.year, t.month;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download