CODEWITHSUNDEEP
sqlsql-serverjoin
LCIII
LCIII

Reputation: 3626

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.

Upvotes: 0

Views: 466

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 2

Related Questions