CODEWITHSUNDEEP
sql-server
IrfanRaza
IrfanRaza

Reputation: 3058

How to get all the child IDs

I have a table with following columns -

  1. ID (ID of the Device)
  2. DeviceName (Name of the device)
  3. ParentID (ID of the parent device)

As per table my data is stored in a tree structure i.e. one device contains several other devices which again contains several devices.

What i need is to get IDs of all the devices coming under given device ID. I need proper SQL query to get all the child nodes (including its own child nodes) coming under a parent node.

For ex. Lets say A is the top node having childs B1, B2.

B1 contains C1, C2 childs while B2 contains C3, C4.

Again C1 contains D1, D2 .... and so on.

What I need is to get all B1,B2,C1,C2,C3,C4,D1,D2,.... if A is provided.

Thanks for sharing your time.

Upvotes: 1

Views: 2131

Answers (1)

Justin Swartsel
Justin Swartsel

Reputation: 3431

You need to use a recursive CTE for this.

;WITH r as (
     SELECT ID
     FROM DevicesTable
     WHERE ParentID = @someID

     UNION ALL

     SELECT d.ID 
     FROM DevicesTable d
        INNER JOIN r 
           ON d.ParentID = r.ID
)
SELECT ID
FROM r

Upvotes: 6

Related Questions