How to get all children of parent item when all are in the same row and table

Question

I have a table of parts and sub-parts where each record contains the primary part for that record along with its ChildPart.

Part - ChildPart
A    -    B
A    -    C
A    -    D
C    -    F
C    -    Z
F    -    R
Z    -    R
Q    -    B
Q    -    C

So for the example above, part A has 7 total descendants (B, C, D, F, Z, R, R). A parent part can have multiple children and a child part can belong to more than 1 parent; notice that part B is used for both A and Q.

How can I efficiently show all the child parts of a given parent part just using joins and not using SQL cursors or loops? The hierarchical tree could theoretically be infinitely deep.

Have a look at [link](http://stackoverflow.com/questions/25079528/hierarchyid-get-all-descendants-for-a-list-of-parents) It uses a CTE so not sure it qualifies for your 'non loop' criteria. It uses Hierarchy Level IDs. — BeaglesEnd, Feb 08 '16 at 14:55

score 2 · Accepted Answer · answered Feb 08 '16 at 15:00

You can use a Recursive CTE:

DECLARE @pID VARCHAR(20) = 'A'

;WITH CTE AS (
   SELECT ChildPart
   FROM mytable
   WHERE Part = @pID

   UNION ALL

   SELECT t1.ChildPart
   FROM mytable AS t1
   INNER JOIN CTE AS t2 ON t1.Part = t2.ChildPart
)
SELECT ChildPart
FROM CTE

How to get all children of parent item when all are in the same row and table

1 Answers1