CTE recursive query question

I have the following tables

sectors table
sectorid    int    PK
sector      varchar(50)
parentid   int       (if parent sector null value is inserted)

example data
sectorid        sector         parentid
1                   IT                 <null>
2                  Engineering  <null>
3                  Programming    1
4                  asp.net             3

jobs table
jobid     int   PK
jobtitle  varchar(50)
sectoridfk int  (FK to sectors table)

example data
jobid      jobtitle             sectoridfk
1           programmer     4 etc..

What i want to achieve is to bring back all top level sectors (ie.. where parentid is null) and display a total count of how many jobs they are associated with (including all their child sectorids).

Display Data: -
Engineering (0)
IT (1)
etc..

Trying to think of the best way to tackle this, maybe using sometype of CTE recursive query but can't think how to bring it all together in the one query ??

I have a basic cte soo far that only takes in one sectorid and determines all its child sectorids which could be used to perform a join on the jobs table ??: -

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
)

Help !!

JT


Start Free Trial