Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Using Common Table Expressions for Parent-Child Relationships

From Wiki

Jump to: navigation, search

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:

tsqlLine number Off | Hide | Select all
  1. declare @test table (bunchof uniqueidentifier default newid(), columns uniqueidentifier default newid(), Id int, ParentID int)
  2.  
  3. insert @test (Id, ParentId)
  4. select 1, null
  5. union all select 5, 1
  6. union all select 15, 2
  7. union all select 16, 5
  8. union all select 27, 16

And you want to get all child rows for 1 (so ItemId 5, 16, 27)

tsqlLine number Off | Hide | Select all
  1. declare @parentId int
  2. set @parentId = 1
  3.  
  4. ;--last statement MUST be semicolon-terminated to use a CTE
  5. with CTE (bunchof, columns, Id, ParentId) as
  6. (
  7.     select bunchof, columns, Id, ParentId
  8.     from @test
  9.     where ParentId = @parentId
  10.     union all
  11.     select a.bunchof, a.columns, a.Id, a.ParentId
  12.     from @test as a
  13.     inner join CTE as b on a.ParentId = b.Id
  14. )
  15. select * from CTE

and if you want to include the parent:

tsqlLine number Off | Hide | Select all
  1. declare @Id int
  2. set @Id = 1
  3.  
  4. ;--last statement MUST be semicolon-terminated to use a CTE
  5. with CTE (bunchof, columns, Id, ParentId) as
  6. (
  7.     select bunchof, columns, Id, ParentId
  8.     from @test
  9.     where Id = @Id
  10.     union all
  11.     select a.bunchof, a.columns, a.Id, a.ParentId
  12.     from @test as a
  13.     inner join CTE as b on a.ParentId = b.Id
  14. )
  15. select * from CTE

You can select depth in the hierarchy as well, if you're into that kind of thing:

tsqlLine number Off | Hide | Select all
  1. declare @Id int
  2. set @Id = 1
  3.  
  4. ;--last statement MUST be semicolon-terminated to use a CTE
  5. with CTE (bunchof, columns, Id, ParentId, Depth) as
  6. (
  7.     select bunchof, columns, Id, ParentId, 0
  8.     from @test
  9.     where Id = @Id
  10.     union all
  11.     select a.bunchof, a.columns, a.Id, a.ParentId, b.Depth + 1
  12.     from @test as a
  13.     inner join CTE as b on a.ParentId = b.Id
  14. )
  15. select * from CTE


As you can see what you're doing here is first selecting your initial recordset, which contains all child rows for your parent ID parameter. You can then union to another query that joins to the CTE itself, to get the children's children (and their grandchildren, and so forth until you reach the last descendant row. Its' important to note that the default recursion limit is 100, so pay attention to the depth of your hierarchy when using these. You can change the recursion limit using the OPTION (MAXRECURSION) hint
tsqlLine number Off | Hide | Select all
  1. WITH CTE AS (
  2. ...
  3. )
  4. 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)

599 Rating: 2.9/5 (23 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Using_Common_Table_Expressions_for_Parent-Child_Relationships"

Powered by MediaWiki GNU Free Documentation License 1.2
Valid XHTML | Valid CSS