Joining 2 tables with duplicates without exponentially increasing number of rows on SQL

Question

I need to join 2 tables where each table's entire row is unique however there are duplicates on the key I am joining on which results in multiple duplicates. Refer to example below:

Table A:                      Table B:
Primary_Key  Variable_A1       Primary Key    Variable_B1   Variable_B2
   A             1                 A              5             6
   B             2                 A              7             8
   C             3                 C              5             0

What happens when joining is the following:

 Primary Key     Variable_A1    Variable_B1   Variable_B2
     A               1              5           6  
     A               1              7           8
     A               1              5           8
     A               1              7           6

Where I only require the below which is the joining of the unique rows:

     Primary Key     Variable_A1    Variable_B1   Variable_B2
         A                1              5              6  
         A                1              7              8

Thus I require a code where SQL acknowledges that Variable_B1 with Variable_B2 is unique and not break it up which results in the duplicates

Possible duplicate of [How does GROUP BY work?](https://stackoverflow.com/questions/7434657/how-does-group-by-work) — tima, Aug 23 '17 at 13:26
I'm also going to point out that a Primary Key should uniquely identify something on a table. — Marshall Tigerus, Aug 23 '17 at 14:06
mmm... something is strange... how can record "A 1 5 8" be in the result set of the query? can you show the exact SQL you are using? — Giacomo Degli Esposti, Aug 24 '17 at 11:01

score 0 · Answer 1 · edited Aug 24 '17 at 17:15

0

If I understand correctly, this might help:

Instead of:

SELECT * FROM A
JOIN B ON A.Primary_key = B.Primary key

Try:

SELECT * FROM A
JOIN (SELCET DISTINCT * FROM B) BB on (A.Primary_key = BB.Primary key)

edited Aug 24 '17 at 17:15

tima

1,498
4
20
28

answered Aug 24 '17 at 10:56

Lars G Olsen

1,093
8
11

Joining 2 tables with duplicates without exponentially increasing number of rows on SQL

1 Answers1