Inner/Outer Join Nightmares - 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 2008 Forums

Transact-SQL (2008)

Inner/Outer Join Nightmares

Author

Topic

mikedcmdva
Starting Member

7 Posts

Posted - 2012-05-30 : 15:28:48

Hi SQL Team!

I am helping my employer with updating an application they use but I am having trouble with one part.

There are three tables involved. This is for a bill of materials selection. There is a top level ID and items associated with that top level ID.

Let's say we are talking about a top level ID of 5707, and an item associated with it with an Item ID of 10969

Here is the top level item:

dbo.Item (Top Level)
- ID : 5707
- Code : 10158CD (top level identifier used in front end application)

dbo.Item (Item associated with top level)
- ID : 10969
- Code : 200010290101960

dbo.BOM
- ID : 1206
- ItemID : 5707 (how this is tied to dbo.Item)

dbo.BOMItem
- BOMID : 1206 (how this is tied to dbo.BOM)
- ItemID : 10969 (how this is tied to dbo.Item)
- Quantity : 1

I wrote the following query that does what I need:

select Code, Quantity from dbo.BOMItem
inner join dbo.Item
on dbo.BOMItem.ItemID = dbo.Item.ID
where BOMID = '1206'
order by Code

That will give me a list of all items associated with BOMID 1206 just fine...here is my dilemma, the front end application works by the top level identifier, such as "10158CD" referenced above in dbo.Item

I want to be able to write a query that will list what is needed like that query above, only by the top level identifier code "10158CD"...because I have all of those identifiers populated in a datagrid for the users to be able to double click to see what makes up that top level ID.....but I have been very unable to find a way to use just that value to return the contents I need like the query does above which doesn't use that top level identifier

Thank you so much for taking the time if you have done so..and let me know if you need other information

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:02:23

[code]
select i.Code, bi.Quantity
from dbo.BOMItem bi
inner join dbo.Item i
on bi.ItemID = i.ID
inner join dbo.BOM b
ON b.ID = bi.BOMID
inner join dbo.Item i2
ON i2.ID = b.ItemID
where i2.Code = '10158CD'
order by Code
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikedcmdva
Starting Member

7 Posts

Posted - 2012-05-30 : 16:18:38

Msg 209, Level 16, State 1, Line 10
Ambiguous column name 'Code'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:23:40

quote:
Originally posted by mikedcmdva

Msg 209, Level 16, State 1, Line 10
Ambiguous column name 'Code'.


select i.Code, bi.Quantity 
from dbo.BOMItem bi
inner join dbo.Item i
on bi.ItemID = i.ID
inner join dbo.BOM b
ON b.ID = bi.BOMID 
inner join dbo.Item i2
ON i2.ID = b.ItemID 
where i2.Code = '10158CD'
order by i.Code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikedcmdva
Starting Member

7 Posts

Posted - 2012-05-30 : 16:40:34

And that worked!

So basically, it needed multiple inner joins?

Can you describe your solution so I can read it and understand for next time, please?

Thank you SO much for your help, Mr.Visakh.. that was surely a headache for me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 17:16:50

quote:
Originally posted by mikedcmdva

And that worked!

So basically, it needed multiple inner joins?

Can you describe your solution so I can read it and understand for next time, please?

Thank you SO much for your help, Mr.Visakh.. that was surely a headache for me

I just added one more join to dbo.Item table as you needed two lookups on it
one based on ItemID field in BOM to get BOM level ID and other based on BOMItem.ItemID to get BOMItem level detail. And I modified the WHERE condition to do search based on Item returned by BOM lookup which was what you wanted as per your requirement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources