| Author |
 Topic  |
|
|
mikedcmdva
Starting Member
USA
7 Posts |
 Posted - 05/30/2012 : 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
India
47040 Posts |
Posted - 05/30/2012 : 16:02:23
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
mikedcmdva
Starting Member
USA
7 Posts |
Posted - 05/30/2012 : 16:18:38
|
Msg 209, Level 16, State 1, Line 10
Ambiguous column name 'Code'. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 05/30/2012 : 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
USA
7 Posts |
Posted - 05/30/2012 : 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
India
47040 Posts |
Posted - 05/30/2012 : 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/
|
|
|
| |
Topic |
|
Inner/Outer Join Nightmares
