sql server - T-SQL Query : Getting Child nodes of a parent

Question

I have a table with the following schema :

ID , CatID, ParentCatID, SiteID

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

for example :

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

In this example CatID 2 is the parent of 4 and 4 is the parent of 5.

How can I get all the SiteIDs that belongs to the root category and all its descendants.

Answer 1

Using a recursive Common 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 terminated. The maximum recursion 100 has been exhausted before statement completion

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