Select statement to return parent and infinite children

by

So referencing this answer:

SQL Server CTE Parent Child recursive

Here’s a working version with your schema:

Table Creation Script

CREATE TABLE YOUR_TABLE
    ([ID] int, [ParentID] int, [Name] varchar(21))
;
    
INSERT INTO YOUR_TABLE
    ([ID], [ParentID], [Name])
VALUES
    (1, NULL, 'A root'),
    (2, NULL, 'Another root'),
    (3, 1, 'Child of 1'),
    (4, 3, 'Grandchild of 1'),
    (5, 4, 'Great grandchild of 1'),
    (6, 1, 'Child of 1'),
    (7, NULL, 'Another root'),
    (8, 7, 'Child of 6')
;

Recursive CTE

DECLARE @ID INT = 1

;WITH ParentChildCTE
AS (
    SELECT ID, ParentId, Name        
    FROM YOUR_TABLE
    WHERE Id = @ID

    UNION ALL

    SELECT T1.ID, T1.ParentId, T1.Name        
    FROM YOUR_TABLE T1
    INNER JOIN ParentChildCTE T ON T.ID = T1.ParentID
    WHERE T1.ParentID IS NOT NULL
    )
SELECT *
FROM ParentChildCTE

The key part is in the CTE creation where the UNION ALL joins back on to the result set, joining ID to ParentId, which doesn’t limit the number of level.

More Related Contents:

  1. Difference between CTE and SubQuery?
  2. Update records in table from CTE
  3. Common Table Expression, why semicolon?
  4. When to use Common Table Expression (CTE)
  5. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  6. Multiple CTE in single query
  7. CTE error: “Types don’t match between the anchor and the recursive part”
  8. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  9. How to use multiple CTEs in a single SQL query?
  10. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  11. Count consecutive duplicate values in SQL
  12. Use one CTE many times
  13. Case statement for multiple columns
  14. SQL Server: Make all UPPER case to Proper Case/Title Case
  15. How do I delete from multiple tables using INNER JOIN in SQL server
  16. Does Foreign Key improve query performance?
  17. SQL : BETWEEN vs =
  18. SQL Server convert string to datetime
  19. Should I design a table with a primary key of varchar or int?
  20. How do I make a composite key with SQL Server Management Studio?
  21. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  22. Return number of rows affected by UPDATE statements
  23. Execute Dynamic Query with go in sql
  24. TransactSQL to run another TransactSQL script
  25. How can I get a list of element names from an XML value in SQL Server
  26. SQL server identity column values start at 0 instead of 1
  27. Is there a severe performance hit for using Foreign Keys in SQL Server?
  28. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  29. Date serial in SQL?
  30. Parameterise table name in .NET/SQL?

Leave a Comment