Geek Answers Handbook

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

The table shows the column identifiers, heading, and ParentID. ParentID is used to identify the record in the same table that is considered its parent, so any record in which ParentID is NULL is one that is itself the parent.

I need a query that will go through each parent and display any below it (ideally with a hyphen or something that denotes its subordination) does anyone know how to do this or how to point me in the right direction?

(I studied T-SQL and read a lot of similar online questions, however my SQL is not sharp enough to figure this out, so I would really appreciate some pointers!)

+4
source share
2 answers

There 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
+2
source

To get hierarchy data from a self-regulation 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
+1
source

More articles:


All Articles