Left outer Join query returns duplicates in SQL Server

Question

I have a table 1 (MID, SSN, ...) MID is primary key and table 2 (ID, SSN, StateCode..) where ID and SSN make up the primary key. I'm trying to display all columns from table 1 along with StateCode from table 2 matching it against SSN. Tbl 1 has 50 rows and some have same SSN values.

If no SSN match is found from table 2, displaying a NULL in StateCode is acceptable, so I chose left join. Here is my query

Select 
    tbl1.*, tbl2.StateCode
from 
    tbl1
left outer join 
    tbl2 on tbl1.SSN = tbl2.SSN

I'm looking to retrieve 50 records, but I get 70, rows that contain the same ssn value in tbl1 ends up duplicated in the final output. What is going wrong?

SSN must be unique in each table for your join to return non-duplicates — Michael Kang
– Michael Kang, Commented Jul 18, 2014 at 2:06
@pixelbits If join isn't the way to do this, what do you suggest for another option? — sandr
– sandr, Commented Jul 18, 2014 at 3:06
What do you want returned in your select list? Your select list will determine how the query is constructed. It will involve a couple of sub-selects and then a join - ugly, but it'll work. — Michael Kang
– Michael Kang, Commented Jul 18, 2014 at 3:09
I'm trying to return all columns in table 1, and Statecode from table 2, displaying either NULL or a value. SSN is the common column in both these tables, in table 1 there are duplicate ssn. — sandr
– sandr, Commented Jul 18, 2014 at 3:13
If table1 has 50 rows and the left join with table2 returns 70, then some of the table1 rows must have matched with 2 or more rows from table2 each. For it is the join that has produced the duplicates. And looking at the join condition, I can only conclude that you are mistaken about SSN being unique in table2. But surely it should be easy to verify with something like SELECT COUNT(SSN), COUNT(DISTINCT SSN) FROM table2. — Andriy M
– Andriy M, Commented Jul 18, 2014 at 9:56

ForguesR · Accepted Answer · 2014-07-19 01:26:48Z

5

I'd suggest reading on cartesian product.

If you have 50 rows in the first table and 70 in the second that makes 3500 rows. The join condition tbl1.SSN = tbl2.SSN will filter out rows but you may well end up with more than 50 rows.

Back to your problem you can see what is happening by trying the following :

SELECT 
  tbl1.*,
  (SELECT COUNT(*) FROM tbl2 WHERE tbl1.SSN = tbl2.SSN) AS NbResultTbl2
FROM 
  tbl1

This will tell which rows of tbl1 has multiple match in tbl2. If you have a number higher than 1 in the NbResultTbl2 column then you are going to end up with duplicates.

To eliminate those duplicates you can try this :

SELECT 
  tbl1.*,
  (SELECT TOP 1 StateCode FROM tbl2 WHERE tbl1.SSN = tbl2.SSN) 
FROM 
  tbl1

This will get the first StateCode found for a matching SNN in tbl2.

edited Jul 19, 2014 at 1:26

answered Jul 18, 2014 at 2:14

ForguesR

3,6281 gold badge20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sandr Over a year ago

i tried reading through although i couldn't infer why a left join would retrieve more rows than what exists in tbl1, since ssn in table 2 is distinct

ForguesR Over a year ago

See updated answer to check if ssn in table is really distinct.

sandr Over a year ago

Thank you, it was not distinct, I came up with another query but yours is short and clean! Very helpful

user3841709 · Accepted Answer · 2014-07-18 02:08:43Z

0

Try using SELECT DISTINCT rather than just SELECT statement, SELECT DISTINCT won't show duplicates

answered Jul 18, 2014 at 2:08

user3841709

3867 silver badges31 bronze badges

5 Comments

sandr Over a year ago

i want all the columns from table 1 along with statecode from table 2, when i do distinct i'm losing data, fewer than 50 rows are being returne

user3841709 Over a year ago

I'm a little confused from your original question where you say displaying a null is acceptable. have you tried adding a Where condition such as WHERE ColumnName IS NOT NULL

sandr Over a year ago

NULL is acceptable as a value in StateCode if an ssn from table 1 is not found in table 2. I'm trying to show all 50 rows along with StateCode that match the SSN for that row

user3841709 Over a year ago

Also if you are looking to retrieve 50 records, do your tables each have more than 50 records or does one have exactly 50 records? If you have exactly 50 in one table then it should be simple just figure out the correct joining

sandr Over a year ago

Yes table 1 has exactly 50 rows, and table 2 contains distinct SSN and the Statecode value. I'm not sure why my left join is retrieving duplicates and final result more than what is present in table 1

Vinoth_S · Accepted Answer · 2014-07-18 04:05:50Z

0

Try, Your both Table have one more primary key is there So just try ID column to match

Select tbl1.MID,tbl1.SSN, tbl2.StateCode
from tbl1
left outer join tbl2 
on tbl1.MID= tbl2.ID
Group by tbl1.MID,tbl1.SSN, tbl2.StateCode

answered Jul 18, 2014 at 4:05

Vinoth_S

1,5601 gold badge14 silver badges15 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2014-07-18 11:22:04Z

0

This is too long for a comment.

"ID and SSN are both primary keys" . . . This statement indicates a lack of understanding of what a primary key is. A table can have only one primary key. A primary key can be composite (composed of more than one column), but there is only one.

If MID is the primary key for table1, then presumably multiple rows can have the same SSN.

Your query is:

Select *
from tbl1, tbl2.StateCode
from tbl1, tbl2 left outer join tbl2 on tbl1.SSN = tbl2.SSN

This is not even valid SQL. You might try this version:

Select distinct tbl1.*, tbl2.StateCode
from tbl1 left outer join
     tbl2
     on tbl1.SSN = tbl2.SSN;

This is valid and would appear to be what you want.

edited Jul 18, 2014 at 11:22

answered Jul 18, 2014 at 2:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

sandr Over a year ago

I tried this, and I still get 70 rows on doing the left outer join in this way, all the rows with same ssn gets duplicated more. I'm not sure why this is

Gordon Linoff Over a year ago

@sandr . . . Maybe select distinct will remove the rows that you think are duplicates.

Chris Latta · Accepted Answer · 2014-07-18 11:45:49Z

0

Try grouping SSN within Table2 and getting the MAX StateCode:

SELECT Table1.*, DT.StateCode
FROM Table1
LEFT OUTER JOIN (
    SELECT SSN, MAX(StateCode) AS StateCode FROM Table2 GROUP BY SSN
) DT ON Table1.SSN = DT.SSN

edited Jul 18, 2014 at 11:45

answered Jul 18, 2014 at 2:11

Chris Latta

20.6k4 gold badges67 silver badges71 bronze badges

2 Comments

sandr Over a year ago

query didn't work, grouping wasn't possible as the other columns didn't comply under this condition, i'm trying to select all of table 1 and one column from table 2

Chris Latta Over a year ago

Okay, try a nested select that returns only one state code for each SSN

Rodrigo · Accepted Answer · 2014-07-18 12:17:05Z

0

To add to the comment by pixelbits. You can perform a subquery with (skip MID)

 select distinct ssn,* from tbl1

And left join that to tbl2

That should give your 50 rows unless other columns besides MID are distinct

edited Jul 18, 2014 at 12:17

answered Jul 18, 2014 at 2:08

Rodrigo

3341 silver badge6 bronze badges

1 Comment

sandr Over a year ago

When i do distinct im actually losing data its returning fewer rows.

Collectives™ on Stack Overflow

Left outer Join query returns duplicates in SQL Server

6 Answers 6

3 Comments

5 Comments

Comments

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

5 Comments

Comments

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related