Robin Robin -4 years ago 85
SQL Question

Combine tables with different data

I have two tables:

+--------+-----+
| name | A |
+--------+-----+
| abc | 10|
| def | 17|
| ghi | 27|
+--------+-----+

+--------+-----+
| name | B |
+--------+-----+
| abc | 9 |
| def | 55|
| xyz | 92|
+--------+-----+


Now I need to combine these two tables, that each name appears only once, but it has the result of A and B in it. If there is no matching entry than a zero should be displayed.

So the result should look like this:

+--------+-----+-----+
| name | A | B |
+--------+-----+-----+
| abc | 10| 9 |
| def | 17| 55|
| ghi | 27| 0 |
| xyz | 0 | 92|
+--------+-----+-----+


Later I might have a third table with C which should be added in the same way.

How would the SQL look like?

Answer Source

You need to use a full outer join, which will preserve rows not joining on both tables. You can find here more details about this.

Your query should look like this

select  coalesce(t1.name, t2.name) name
        coalesce(t1.a, 0) a,
        coalesce(t2.b, 0) b
from    table1 t1
full outer join
        table2 t2
on      t1.name = t2.name

The coalesce function returns the first parameter or, if that is null, the second, and it's needed because if a row isn't joined, it will be preserved by the outer join but you'll have nulls on it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download