JOIN QUESTION - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

JOIN QUESTION

Author

Topic

vicpal25
Starting Member

21 Posts

Posted - 2007-12-19 : 17:12:52

I got three tables (item, components and assemblies) . I am trying to see where in the assemblies are the components used. I keep on getting duplicate results for my items. Any Ideas on how to go about this? Here is my SQL:

select bmd.fEngr_BOM_ID as 'Assembly No'
FROM Engr_BOM_Detail bmd
INNER JOIN Engr_Item itm
ON itm.pEngr_Item_ID=bmd.fEngr_Item_ID
INNER JOIN Engr_BOM_Control bmc
ON bmc.pEngr_BOM_ID=bmd.fEngr_BOM_ID
WHERE itm.Item_No='1000091'

Assembly No
---------------------------------------
34
34
20
20

DO I NEES A LEFT JOIN?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-19 : 17:22:24

You can comment out the second join and run the SELECT and if you dont see dups then its prbly the second join thats returning dups.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vicpal25
Starting Member

21 Posts

Posted - 2007-12-19 : 17:24:23

Nope, I get the same results..

Assembly No
---------------------------------------
34
34
20
20

SQL CODE:

select bmd.fEngr_BOM_ID as 'Assembly No'
FROM Engr_BOM_Detail bmd
INNER JOIN Engr_Item itm
ON itm.pEngr_Item_ID=bmd.fEngr_Item_ID
WHERE itm.Item_No='1000091'

quote:
Originally posted by dinakar

You can comment out the second join and run the SELECT and if you dont see dups then its prbly the second join thats returning dups.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-19 : 17:57:10

ok looks like your Engr_Item table has multiple rows for a given fEngr_Item_ID.
Try LEFT JOIN


select bmd.fEngr_BOM_ID as 'Assembly No'
FROM Engr_BOM_Detail bmd
LEFT JOIN Engr_Item itm ON itm.pEngr_Item_ID=bmd.fEngr_Item_ID
WHERE itm.Item_No='1000091'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 18:00:03

[code]SELECT DISTINCT bmd.fEngr_BOM_ID AS [Assembly No]
FROM Engr_BOM_Detail AS bmd
INNER JOIN Engr_Item AS itm ON itm.pEngr_Item_ID = bmd.fEngr_Item_ID
INNER JOIN Engr_BOM_Control AS bmc ON bmc.pEngr_BOM_ID = bmd.fEngr_BOM_ID
WHERE itm.Item_No = '1000091'[/code]

E 12°55'05.25"
N 56°04'39.16"

vicpal25
Starting Member

21 Posts

Posted - 2007-12-19 : 18:10:52

Bingo! Forgot about that DISTINCT sucker. Gracias!

quote:

Originally posted by Peso

SELECT DISTINCT	bmd.fEngr_BOM_ID AS [Assembly No]
FROM		Engr_BOM_Detail AS bmd
INNER JOIN	Engr_Item AS itm ON itm.pEngr_Item_ID = bmd.fEngr_Item_ID
INNER JOIN	Engr_BOM_Control AS bmc ON bmc.pEngr_BOM_ID = bmd.fEngr_BOM_ID
WHERE		itm.Item_No = '1000091'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources