Tomb_Raider_Legend Tomb_Raider_Legend - 6 months ago 8
SQL Question

Combine two tables on same columns

I have 2 tables as showen below:

TABLE 1: Name Age Weight(Kilo)
Tom 16 56
Alex 29 89

TABLE 2: Name Age Sex
Tom 16 M
Alex 29 M


What I want to get:

TABLE 3: Name Age Sex Weight(Kilo)
Tom 16 M 56
Alex 29 M 89


I have tried Union/Union All and it doesn't work. Also tried to use Join but it gives me a table with duplicate values. Any idea how to do this?

Answer

Assuming your Name/Age values match up exactly between the two tables, a JOIN would be exactly what you're looking for.

select t1.Name, t1.Age, t2.Sex, t1.Weight
from Table1 t1
join Table2 t2 on t1.Name = t2.Name and t1.Age = t2.Age

If there is any possibility that there is no match between the tables, start with the one with the larger number of records, then do a left outer join:

For example, assume Table1 has every person, but Table2 may be missing some:

select t1.Name, t1.Age, t2.Sex, t1.Weight
from Table1 t1
left join Table2 t2 on t1.Name = t2.Name and t1.Age = t2.Age

If you might have records in either table that aren't in the other, a full outer join would work:

select
    coalesce(t1.Name, t2.Name) [Name]
    ,coalesce(t1.Age, t2.Age) [Age]
    ,t2.Sex
    ,t1.Weight
from Table1 t1
full join Table2 t2 on t1.Name = t2.Name and t1.Age = t2.Age