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 |
|
JBelthoff
Posting Yak Master
173 Posts |
 Posted - 2012-06-27 : 13:43:19
|
I have a table representing a very large assembled product with numerous subassemblies. The table is a Parent/Child hierarchy and nests down 10 levels deep. I need to get a list of Parts marked as [Rep] = 1 for each next parent up the hierarchy that is marked with [CanOverhaul] = 1 like this:ID ARID PartNo QTY4 60 60-PartNo 44 61 61-PartNo 44 62 62-PartNo 44 70 70-PartNo 179 90 90-PartNo 179 92 92-PartNo 679 109 109-PartNo 179 149 149-PartNo 1 79 150 150-PartNo 179 151 151-PartNo 179 152 152-PartNo 179 232 232-PartNo 179 233 233-PartNo 279 234 234-PartNo 179 1440 1440-PartNo 4879 1454 1454-PartNo 279 1463 1463-PartNo 24etc... I have a DDL you can use to set up the sample data located here: [url]http://www.datalissol.com/sql/Auto%20Replacement%20Parts.txt[/url]It is kind of large.... Sorry... Any help would be appreciated.Thanks,JBelthoff› As far as myself... I do this for fun! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-06-27 : 17:39:54
|
| Sorry but I'm not following the rules.can you post just a small amount of data (rather than the full 11K rows) and the actual desired output based on that small sample?Be sure to include cases where the middle of the hierarchy contains canoverhaul=0 and/or rep=0 so we can see if that breaks the linage or if we just filter those out but still work up/down the hierarchy. And what is the logical starting place to start working up (or down) the hierarchy? Is it all rows where sublevel = 10 or just for any given part...Can I assume that ID and PID is the childid and parentid respectively? And what is ARID (posted above)? That is not in your table.Be One with the OptimizerTG |
 |
|
|
|
|
|
Help with Query – Nested List from Parent Child