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.
| Author |
Topic |
|
scott_hanebutt
Starting Member
10 Posts |
 Posted - 2009-07-30 : 13:10:40
|
| I have a table that contains bill of material information. I am trying to find all parts that contain parts that start with “SAB” or “SAC”, the full name of the “SAB” or “SAC” part and the quantity of those parts used in the parent. An “SAB” or “SAC” can have multiple first level parents.The first level parents can have multiple second level parentsThe second level parents never have parents.Table Structure -Table Name: INBOMSFields: FPARENT FCOMPONENT FQTYSample DataFPARENT FCOMPONENT FQTYSAF SAB001 2UM2172 SAF 2M1456 SAF 4M1456 SAC005 2Sample of desired outputParent Component Qty of ComponentSAF SAB001 2UM2172 SAB001 4M1456 SAB001 8M1456 SAC005 2Thank you,Scott |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-07-30 : 14:47:52
|
| Your logic is not obvious to me so I won't post any sample code. But search for "trees" or "hierarchy" in the 2000 forums - there should be plenty of samples. If your maximum number of levels is just 3 then perhaps just one statement with 2 left outer self joins would do it.Be One with the OptimizerTG |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-07-30 : 15:30:50
|
| This method handles recursive relationships in SQLSVR 2000:http://sqlblindman.googlepages.com/returningchildrecords________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
|
|
Recursive Query?