June 19, 2008 at 1:55 pm
I have a table BOM containing two fields, ALLOY and MATERIAL, which contains all possible materials used to make my company's alloys. In other words, the rows in that table look like
A1,M1
A1,M2
A2,M1
A2,M3
etc...
What is the fastest, most efficient, way to find all the alloys (and their materials) that use material "xyz"?
The only way I've found so far, and it is slow and clunky, is as follows:
SELECT
B.ALLOY,
B.MATERIAL
FROM
BOM B
WHERE
(SELECT B1.MATERIAL FROM BOM B1 WHERE B1.MATERIAL="xyz" AND B.ALLOY=B1.ALLOY) IS NOT NULL
June 19, 2008 at 2:00 pm
;with Alloys (A) as
(select alloy
from dbo.BOM
where material = 'xyz')
select Alloy, Material
from dbo.BOM
inner join Alloys
on alloy = a
That's SQL 2005. If you need an SQL 2000 version, move the Alloys part down into the From clause and turn it into a standard derived table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 2:09 pm
Lookup Common Table Expressions in BOL. You probably will need to use a recursive CTE - but it should work.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 19, 2008 at 2:24 pm
Thanks!
I've read about the techniques you describe, but haven't used them yet.
Now's the perfect time, I guess...:cool:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply