Before I begin, I have no control over the structure of this table
. It belongs to a program my company uses. I cannot modify the structure of the existing tables. I have looked at many excellent solutions offered on EE, but none of them are quite getting me there. I have never used CTE before, but it seems the best option for this situation. However, I'm open to anything that works. Here is the CTE I put together based on solutions I found on EE for similar problems.
;WITH IH(ParentPart, Component)
WHERE ParentPart = '414E5000-911 REV 005'
FROM BomStructure A
INNER JOIN IH B
ON B.Component = A.ParentPart)
I have a table with two fields, ParentPart and Component. When a Component part has children, then it is also listed as a ParentPart. If I query on a top level part, I get something like this (with some rows removed for brevity):
Parent Part Component
414E5000-911 REV 005 414E5001-6 REV A
414E5000-911 REV 005 414E5002-4 REV -
414E5002-4 REV - 414E5006-23 REV A
414E5002-4 REV - 414E5008-1 REV -
414E5002-4 REV - 414E5008-5 REV -
414E5002-4 REV - 414E5010-1 REV -
414E5002-4 REV - 414E5010-2 REV -
414E5002-4 REV - 414E5012-1 REV A
414E5002-4 REV - 414E5048-1 REV -
414E5006-23 REV A 414E5006-10 REV A
414E5011-6 REV - 414E5011-8 REV -
This is giving me the parent with its two children, and eventually it returns the children of the children, but this isn't what I need. I need parent - first child - all descendants of first child - second child - all descendants, etc.
The actual hierarchy I need is:
414E5000-911 REV 005
414E5011-6 REV -
414E5002-4 REV -
(This continues on through several more grandchildren and great grandchildren of the top level ParentPart).
If CTE is the right way to go with this, would someone please help me get it right. If CTE isn't the best option, please make suggestions.