LCIII February 2016

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

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.

Answers


Giorgos Betsos February 2016

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

Post Status

Asked in February 2016
Viewed 3,750 times
Voted 7
Answered 1 times

Search




Leave an answer