CODEWITHSUNDEEP
sqlsql-server
Tomb_Raider_Legend
Tomb_Raider_Legend

Reputation: 461

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?

Upvotes: 1

Views: 2917

Answers (4)

mpatel
mpatel

Reputation: 19

SELECT t1.*,t2.Sex FROM table1 t1 LEFT JOIN table2 t2 ON t1.Name=t2.Name

Upvotes: 0

Joe Enos
Joe Enos

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

kayla210
kayla210

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

S3S
S3S

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

Related Questions