Home Ask Login Register

Developers Planet

Your answer is one click away!

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.


Giorgos Betsos February 2016

You can use a Recursive CTE:


   SELECT ChildPart
   FROM mytable
   WHERE Part = @pID


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

Post Status

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


Leave an answer

Quote of the day: live life