T-SQL query: getting child nodes of parent element

Question

I have a table with the following schema:

ID , CatID, ParentCatID, SiteID 

I want to get all sites belonging to categories that are roots (means their ParentCatID = 0) and all their descendants.

eg:

 ID , CatID, ParentCatID, SiteID -------------------------------- 1 , 2 , 0 , 3 1 , 4 , 2 , 6 1 , 5 , 4 , 7 

In this example, CatID 2 is parent 4 and 4 is parent 5.

How can I get all site identifiers that belong to the root category and all its descendants.

Answer 1

Using the recursive shared table expression supported on SQL Server 2005+ :

 WITH hierarchy AS ( SELECT yt.id, yt.catid, yt.parentcatid, yt.siteid FROM YOUR_TABLE yt WHERE yt.parentcatid = 0 UNION ALL SELECT yt.id, yt.catid, yt.parentcatid, yt.siteid FROM YOUR_TABLE yt JOIN hierarchy h ON h.catid = yt.catid) SELECT t.* FROM hierarchy t OPTION (maxrecursion 1000) 

If you get:

The statement is complete. The maximum recursion of 100 was exhausted before the application

The default is 100 recursions. The maximum number of recursions can be set using the maxrecursion option, up to a maximum of 32767.