CTE recursive query question

thanks chapmandew for the article. Not sure how i would put it into pratice on my problem though..

The thing that confuses me is that i need to select all top level sectors (easy) : -
SELECT sectorid, sector
FROM SECTORS
WHERE parentid is null

i then need to find all child sectorids for EACH parent sectorid and perform a total count on how many jobs are associated with each (maybe using the IN CLAUSE ?) but the problem i am facing is that the CTE cannot be a subquery of the above select statement (correct me if i am wrong) ?. Maybe i need to create a function for the cte and use it within a subquery ?

JT

Ok, so here is the original I had in the article:

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
 AS
 (
      SELECT
       s.SPID, s.BlockingSPID, s.Definition,
       ROW_NUMBER() OVER(ORDER BY s.SPID),
       0 AS LevelRow
     FROM
       #Processes s
       JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
     WHERE
       s.BlockingSPID = 0
     UNION ALL
     SELECT
       r.SPID,  r.BlockingSPID, r.Definition,
       d.RowNo,
       d.LevelRow + 1
     FROM
       #Processes r
      JOIN Blocking d ON r.BlockingSPID = d.SPID
     WHERE
       r.BlockingSPID > 0
 )
 SELECT * FROM Blocking
 ORDER BY RowNo, LevelRow


No, we can change it a bit for your scenario:

WITH Blocking(sectorid, parentid, sector,  RowNo, LevelRow)
 AS
 (
SELECT sectorid, ParentID, sector
       ROW_NUMBER() OVER(ORDER BY s.sectorid),
       0 AS LevelRow
FROM SECTORS s
s.ParentID IS NULL

     UNION ALL
     SELECT
       r.sectorid,  r.ParentID, r.sector,
       d.RowNo,
       d.LevelRow + 1
     FROM
       Sector r
      JOIN MyCTE d ON r.ParentID= d.sectorid
     WHERE
       r.parentid> 0
 )
 SELECT * FROM MyCTE
 ORDER BY RowNo, LevelRow

Let me know if this works....if may have missed some things, but it should put you on the right track.

Thanks chapmandew
My output data needs to look like this: -
IT(10)
ENGINEERING (20)

I am doing a count on the number of jobs associated with EACH individual sectorid parent and their children (sector.sectorid = jobs.sectoridfk), the CTE is bringing back all the sectorids associated with each sectorparent id and maybe i can perform an IN clause ?

Sorry chapmandew for not explaining this well enough....

1. I want to display just the top level sectors (where parentid is null)
2. For each top level sector displayed i want to perform a count of how jobs are associated with it and its children sectors (thats why i am thinking about the recursive cte to get all its children sectorids)

I know how to display all the top level sectors: -

SELECT sectorid, sector
FROM SECTORS
WHERE parentid is null

I know how to bring back all children sectorids for a SPECIFIC parent sectorid (i think?): -

WITH SectorHierarchy (sectorId) AS
(
   -- Base case
   SELECT      sectorId
   FROM      Sectors
   WHERE      sectorId  = @sectorParentId

   UNION ALL

   -- Recursive step
   SELECT      s.sectorId
   FROM      sectors s
   INNER JOIN SectorHierarchy sh
      ON s.parentId = sh.sectorId
)

Its putting it all into one query that is consfusing me. Do i need to put the recursive cte into a function and call it in the parent sectors select statement ??. Whats the best way ?

Thanks for your patience

JT