Join returning duplicates when source tables don't

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join returning duplicates when source tables don't

Author

Topic

getut
Starting Member

7 Posts

Posted - 2014-10-23 : 17:25:31

I have T1 and T2. I am trying to join them in a view that shows everything in T1, except also with the corresponding price. I keep getting output that has items TIMES the number of stores for store 1, them items TIMES the number of stores for store 2, etc. What am I doing wrong. I've tried inner, left, right and all give exactly the same result. Instead of getting 6 rows of output I get 12 rows... 6 rows for Store 1 and 6 rows for Store 2.

Sample query that is messing up for me.

select T1.StoreID, T1.ItemID, T1.ItemDesc, T2.Price from T1 left join T2 on T1.ItemID = T2.ItemID

Sample T1

StoreID, ItemID, ItemDesc
1,10,'Apples'
1,20,'Oranges'
1,30,'Bananas'
2,10,'Apples'
2,20,'Oranges'
2,30,'Bananas'

Sample T2

StoreID, ItemID, Price
1,10,1.25
1,20,1.10
1,30,1.05
2,10,1.20
2,20,1.12
2,30,1.06

I'm WANTING the following output:

StoreID, ItemID, ItemDesc, Price
1,10,'Apples',1.25
1,20,'Oranges',1.10
1,30,'Bananas',1.05
2,10,'Apples',1.20
2,20,'Oranges',1.12
2,30,'Bananas',1.06

But I GET this:
StoreID, ItemID, ItemDesc, Price
1,10,'Apples',1.25
1,10,'Apples',1.20
1,20,'Oranges',1.10
1,20,'Oranges',1.12
1,30,'Bananas',1.05
1,30,'Bananas',1.06
2,10,'Apples',1.25
2,10,'Apples',1.20
2,20,'Oranges',1.10
2,20,'Oranges',1.12
2,30,'Bananas',1.05
2,30,'Bananas',1.06

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-23 : 17:36:03

You need to add StoreID to the join condition:

select T1.StoreID, T1.ItemID, T1.ItemDesc, T2.Price
from T1
left join T2 on T1.StoreID = T2.StoreID and T1.ItemID = T2.ItemID

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources