Reputation: 461
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?
Upvotes: 1
Views: 2917
Reputation: 19
SELECT t1.*,t2.Sex FROM table1 t1 LEFT JOIN table2 t2 ON t1.Name=t2.Name
Upvotes: 0
Reputation: 40431
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
Upvotes: 5
Reputation: 106
JOIN is exactly what you need, but you need to join on a unique identifier. In this case, the Name column is unique for those two records, but usually JOINs are done on generated IDs such as CustomerID or ClientID. Like others have posted, you can use the other columns in conjunction with the Name column to ensure you don't get duplicates by adding those columns in the ON section of the JOIN clause.
SELECT t1.Name, t1.Age, t2.Sex, t1.Weight
FROM Table1 t1
INNER JOIN Table2 t2
ON (t1.Name = t2.Name)
I suggest adding a unique identifier as a primary key so JOINs are a lot easier to do in the future.
Upvotes: 2
Reputation: 25152
Since it doesn't look like you have a PRIMARY KEY
SELECT
t1.*,
t2.Sex
FROM
table1 t1
INNER JOIN
tabel2 t2 on t1.Name = t2.Name and t1.Age = t2.Age
This is most likely still going to cause some duplicates in larger datasets, but I'm assuming you are looking for logic and posted some dummy data.
Upvotes: 3