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.

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


Quote of the day: live life