Advanced Search

get all children under a tree

/ Published in: SQL
Save to your folder(s)
to fetch all the children in a tree based on parentid

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. http://stackoverflow.com/questions/926033/in-sql-server-how-can-i-select-all-records-in-a-recursive-TABLE
  2.  
  3. inline code IF url IS NOT working:
  4. CREATE TABLE TestTable
  5. ( 
  6.     ID INT PRIMARY KEY NOT NULL,
  7.     ParentID INT
  8. )
  9.  
  10. INSERT INTO TestTable VALUES (0, NULL)
  11. INSERT INTO TestTable VALUES (1, 0)
  12. INSERT INTO TestTable VALUES (2, 0)
  13. INSERT INTO TestTable VALUES (3, 1)
  14. INSERT INTO TestTable VALUES (4, 3)
  15.  
  16.  
  17. -- Get branch
  18. ;WITH TreeRecCTE (ID, ParentID, IDPath)
  19. AS
  20. (
  21.    SELECT ID, ParentID, CONVERT(VARCHAR(MAX), ID) AS IDPath
  22.       FROM TestTable
  23.       WHERE ParentID IS NULL
  24.    UNION ALL
  25.    SELECT
  26.     		Child.ID,
  27.     		Child.ParentID,
  28.     		Parent.IDPath + '.' + CONVERT(VARCHAR(100),Child.ID) AS IDPath
  29.     	FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  30.   )
  31. SELECT * FROM TreeRecCTE WHERE IDPath LIKE '%.1.%' ORDER BY ParentID ASC 
  32.  
  33.  
  34. -- Get complete tree:
  35. ;WITH TreeRecCTE (ID, ParentID, IDPath)
  36. AS
  37. (
  38.    SELECT ID, ParentID, CONVERT(VARCHAR(MAX), ID) AS IDPath
  39.       FROM TestTable
  40.       WHERE ParentID IS NULL
  41.    UNION ALL
  42.    SELECT
  43.     		Child.ID,
  44.     		Child.ParentID,
  45.     		Parent.IDPath + '.' + CONVERT(VARCHAR(100),Child.ID) AS IDPath
  46.     	FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  47.   )
  48. 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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.