CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Rich Jenks
Rich Jenks

Reputation: 1773

How to iterate through an SQL table which "parent" and child" rows are in same table

In a table there are the columns ID, Title and ParentID. ParentID is used for the ID of the entry in the same table which is considered its parent - thus any entry in which ParentID is NULL is one which is itself a parent.

I need a query which will iterate through each parent and list any child below it (ideally with a hyphen or something to denote its subordination) does anyone know how this can be done or how to point me in the right direction?

(I've looked into T-SQL and read many similar online questions however my SQL isn't quite sharp enough to make sense of it, so I'd greatly appreciate some pointers!)

Upvotes: 5

Views: 7251

Answers (2)

Azadeh Radkianpour
Azadeh Radkianpour

Reputation: 971

Here you are!

WITH n(ID, Title) AS 
                    (SELECT ID, Title
                    FROM YourTable
                    WHERE ID = (SELECT TOP 1 ID FROM YourTable WHERE ParentID IS NULL)
                    UNION ALL 
                    SELECT nplus1.ID, nplus1.Title
                    FROM YourTable as nplus1, n 
                    WHERE n.ID = nplus1.ParentID) 
                    SELECT ID, Title FROM n

Upvotes: 3

Azadeh Radkianpour
Azadeh Radkianpour

Reputation: 971

To get hierarchy data from self-referencing table, you can use WITH syntax in sql 2008

  WITH n(ID) AS 
   (SELECT ID FROM YourTable 
   UNION ALL 
   SELECT nplus1.ID
   FROM YourTable as nplus1, n 
   WHERE n.ID = nplus1.ParentID) 
   SELECT ID FROM n

Upvotes: 1

Related Questions