Need help with a union SQL statement

[Fatbob]

Hi everyone,

I need help creating a union statement (at least I believe I need a union statement) that will join two tables that have only on common column but possibly several different columns.

For example, here are two example tables...


Table 1
name age
------------------
Steve 32
Tom 35
John 30
Adam 25

Table 2
name weight
----------------------
Steve 200
John 210
Adam 215
Ryan 185


and produce the following results...


name age weight
-----------------------------------
Steve 32 200
Tom 35
John 30 210
Adam 25 215
Ryan 185


Any know how I can produce this result? :confused:

Thanks!

[olivthill]

Because some names in the first table are not present in the second table, you need to do a "left join".
And because some names in the second table are not present in the first table, you need to perform another "left join" in the other way.
You wind up with:
SELECT Table1.name AS name, Table1.age AS age, Table2.weight AS weight
FROM Table1 LEFT JOIN Table2 ON Table1.name = Table2.name;
UNION
SELECT Table2.name AS name, Table1.age AS age, Table2.weight AS weight
FROM Table2 LEFT JOIN Table1 ON Table2.name = Table1.name;
It is working well with MS-Access.
With other databases "left join" might be expressed with "(+)" or "*".

[Fatbob]

Thank you! :thumb: