CODEWITHSUNDEEP
sqlms-access
tee
tee

Reputation: 1336

SQL combining two tables without duplication

I want to combine two tables based on a column. Here's an example:

These are my two tables:

    Table1                  Table2

Person | salary        Person | Age
Tim    |  22.50        Larry  | 32
Tony   |  49.00        Tim    | 22
John   |  32.67        Tony   | 44
Kim    |  23.42        John   | 31

And my resulting table should be:

Person | salary | Age
Larry  |        | 32
Tim    |  22.50 | 22 
Tony   |  49.00 | 44
John   |  32.67 | 31
Kim    |  23.42 |

Everyone is added to the resulting table only once even if they don't have a value for both salary and age

Thanks in advance!

Upvotes: 0

Views: 91

Answers (3)

Mourad Sheriey
Mourad Sheriey

Reputation: 64

I don't see what's your problem exactly but here is a SQL query that will do just what you have requested:

SELECT Person, salary, Age
FROM Table1 FULL OUTER JOIN Table2
WHERE Table1.Person = Table2.Person

Edit: This won't work on MS-ACCESS for its lack of support for FULL OUTER JOINS, the other two answers have explained the alternative.

Upvotes: 0

G.Arima
G.Arima

Reputation: 1171

This could have been done by full outer join but since you are using ms access you will have to use union all in the below manner.
Try this out and let me know in case you face any difficulties.

SELECT * FROM table1 t1
LEFT JOIN 
table2 t2
ON t1.person = t2.person
UNION all
SELECT * FROM table1 t1
RIGHT JOIN 
table2 t2
ON t1.person = t2.person

Upvotes: 0

Darkisa
Darkisa

Reputation: 2037

Since you have tagged this as MS-ACCESS I will provide SQL code for MS ACCESS

Because Microsoft does not have the capability to do a FULL OUTER JOIN, you have to think of a clever way to use existing SQL commands to create a FULL OUTER JOIN. The following code should do the trick using your tables above:

SELECT Table1.Person, Salary, Age
FROM Table1 INNER JOIN Table2 ON Table1.Person = Table2.Person
UNION ALL
SELECT Table1.Person, Salary, Age
FROM Table1 LEFT JOIN Table2 ON Table1.Person = Table2.Person
WHERE Table2.Person Is Null
UNION ALL
SELECT Table2.Person, Salary, Age
FROM Table1 RIGHT JOIN Table2 ON Table1.Person = Table2.Person
WHERE Table1.Person Is Null

Upvotes: 2

Related Questions