CODEWITHSUNDEEP
sqlsql-server
SanMu
SanMu

Reputation: 765

SQL join columns of two tables on unique key

Assume I have two tables, A and B, both with a unique key looking as follows (there are of course more rows and columns in the real table):

Unique_Key       A        B
____________________________
    ABC          1        2
    BCD          5        6
    DDD          10       11


 Unique_Key      C        D
____________________________
    ABC          6        7
    BCD          8        9
    DDD          100      200

I would like to join the columns of the two table on the Unique_Key to product the following output:

Unique_Key       A        B         C         D
_________________________________________________
    ABC          1        2         6         7
    BCD          5        6         8         9
    DDD          10       11        100       200

I have tried this:

select 
    [A], [C]
from 
    tableA r with (nolock) join tableB l with (nolock) on r.Unique_Key = 
l.Unique_Key

It works in so much as it does combine the table but it produces duplicate rows and I am not sure why. That is in essence what I want to avoid - to have duplicate rows.

Thanks!

Upvotes: 0

Views: 3681

Answers (4)

Sayed M. Idrees
Sayed M. Idrees

Reputation: 1408

you can use the Join Command which works well in this case.

SELECT A.Unique_Key, A.Atable_Column1, A.Atable_Column2, B.Btable_Column3, B.Btable_Column4 
From Table1 A  inner join Table2 B
 on A.Unique_Key = B.Unique_Key

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Neither tableA.A nor tableB.C is unique, or so I understand this.

With

Unique_Key | A | B
-----------+---+--
AAA        | 1 | 1
BBB        | 1 | 2
CCC        | 2 | 3

and

Unique_Key | C | D
-----------+---+--
AAA        | 1 | 1
BBB        | 1 | 2
CCC        | 4 | 5

you'd get

A | C
--+--
1 | 1
1 | 1
2 | 4

where you want

A | C
--+--
1 | 1
2 | 4

Use DISTINCT for that:

select distinct tablea.a, tableb.c
from tablea 
join tableb on tablea.unique_key = tableb.unique_key;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If unique_key is really unique in each table, then this will do what you want without producing duplicates:

select r.unique_key, r.a, r.b, l.c, l.d
from tableA r join
     tableB l 
     on r.Unique_Key = l.Unique_Key;

If you are getting duplicates, your key is not unique. You can identify the keys with duplicates by running:

select unique_key
from tableA r
group by unique_key
having count(*) > 1;

and:

select unique_key
from tableB l
group by unique_key
having count(*) > 1;

Based on your choice of names, you probably need to fix the underlying data.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

you can use group by with aggregation

select 
    r.Unique_Key,max([A]), max([C])
from 
    tableA r join tableB l on r.Unique_Key = 
l.Unique_Key
group by r.Unique_Key

Upvotes: 0

Related Questions