Using Common Table Expressions for Parent-Child Relationships
From Wiki
I've seen a ton of questions on forums lately that ask the best way to get a list of all child (grandchild, great grandchild, etc...) rows for a particular parent.
This was a real pain in SQL 2000, but in SQL 2005+ it is a joy, thanks to Common Table Expressions. So say you want to get all descendant records from a particular ID in your table "myTable". With SQL 2000 this got really nasty (I don't have access to the function I wrote for this atm, but I know it was gross!).
With SQL 2005 though it is a dream. Say you have this sample data:
- DECLARE @test TABLE (bunchof UNIQUEIDENTIFIER DEFAULT newid(), columns UNIQUEIDENTIFIER DEFAULT newid(), Id INT, ParentID INT)
- INSERT @test (Id, ParentId)
- SELECT 1, null
- UNION all SELECT 5, 1
- UNION all SELECT 15, 2
- UNION all SELECT 16, 5
- UNION all SELECT 27, 16
And you want to get all child rows for 1 (so ItemId 5, 16, 27)
- DECLARE @parentId INT
- SET @parentId = 1
- ;--last statement MUST be semicolon-terminated to use a CTE
- WITH CTE (bunchof, columns, Id, ParentId) AS
- (
- SELECT bunchof, columns, Id, ParentId
- FROM @test
- WHERE ParentId = @parentId
- UNION all
- SELECT a.bunchof, a.columns, a.Id, a.ParentId
- FROM @test AS a
- INNER join CTE AS b ON a.ParentId = b.Id
- )
- SELECT * FROM CTE
and if you want to include the parent:
- DECLARE @Id INT
- SET @Id = 1
- ;--last statement MUST be semicolon-terminated to use a CTE
- WITH CTE (bunchof, columns, Id, ParentId) AS
- (
- SELECT bunchof, columns, Id, ParentId
- FROM @test
- WHERE Id = @Id
- UNION all
- SELECT a.bunchof, a.columns, a.Id, a.ParentId
- FROM @test AS a
- INNER join CTE AS b ON a.ParentId = b.Id
- )
- SELECT * FROM CTE
You can select depth in the hierarchy as well, if you're into that kind of thing:
- DECLARE @Id INT
- SET @Id = 1
- ;--last statement MUST be semicolon-terminated to use a CTE
- WITH CTE (bunchof, columns, Id, ParentId, DEPTH) AS
- (
- SELECT bunchof, columns, Id, ParentId, 0
- FROM @test
- WHERE Id = @Id
- UNION all
- SELECT a.bunchof, a.columns, a.Id, a.ParentId, b.DEPTH + 1
- FROM @test AS a
- INNER join CTE AS b ON a.ParentId = b.Id
- )
- SELECT * FROM CTE
- WITH CTE AS (
- ...
- )
- SELECT * FROM CTE OPTION (MAXRECURSION 1000)
This is one of my favorite new T-SQL functions in 2005. Have fun with it.
---AlexCuse 16:44, 10 October 2008 (GMT)
