Posted By

hairajeshk on 01/23/13

Tagged

Versions (?)

Last Edited at 01/23/13 11:53pm

Statistics

Viewed 643 times

Favorited by 0 user(s)

Related snippets

get all children under a tree

/ Published in: SQL

to fetch all the children in a tree based on parentid

Highlighted TextExpand

http://stackoverflow.com/questions/926033/in-sql-server-how-can-i-select-all-records-in-a-recursive-table

inline code if url is not working:
CREATE TABLE TestTable
( 
    ID int primary key NOT NULL,
    ParentID int
)

INSERT INTO TestTable VALUES (0, null)
INSERT INTO TestTable VALUES (1, 0)
INSERT INTO TestTable VALUES (2, 0)
INSERT INTO TestTable VALUES (3, 1)
INSERT INTO TestTable VALUES (4, 3)

-- Get branch
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
    		Child.ID,
    		Child.ParentID,
    		Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
    	FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE WHERE IDPath LIKE '%.1.%' ORDER BY ParentID ASC

-- Get complete tree:
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
    		Child.ID,
    		Child.ParentID,
    		Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
    	FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE ORDER BY ParentID ASC

URL: http://stackoverflow.com/questions/926033/in-sql-server-how-can-i-select-all-records-in-a-recursive-table

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.