Calculate number of descendants recursively

Question

I have a table with navigation that joins back on its self using ParentId. I am trying to calculate how many descendents each record has, I know that I need to increment a counter in the recursion, I'm just not sure how to go about it!

Any help would be greatly appreciated!

CREATE TABLE [dbo].[Navigation](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AnchorText] [varchar](50) NOT NULL,
    [ParentID] [int] NULL)

insert into Navigation
select 'Level 1', null
union
select 'Level 2', 1
union
select 'Level 3', 2
union
select 'Level 4', 3


WITH NavigationCTE (Id, AnchorText, ParentId, NumberofDescendants) as
(
      Select Id, AnchorText, ParentId, 'Number of Descendants Here' as NumberofDescendants
      from dbo.Navigation nav

      union ALL 

      select nav.Id, nav.AnchorText, nav.ParentId,  'Number of Descendants Here' as NumberofDescendants
      from dbo.Navigation nav

      join Navigation ON nav.ParentId = nav.Id
)


SELECT * FROM NavigationCTE

EDIT Added Level and Incremented in recursion:

WITH NavigationCTE (Id, AnchorText, ParentId, Level) as
(
      Select nav.Id, nav.AnchorText, nav.ParentId, 0 as Level
      from dbo.Navigation AS nav

      UNION ALL 

      select nav.Id, nav.AnchorText, nav.ParentId, Level + 1
      from dbo.Navigation AS nav

      join Navigation AS nav2 ON nav.ParentId = nav2.Id
)


SELECT * FROM NavigationCTE

Paul Sasik · Accepted Answer · 2011-03-18T15:39:42.150

4

Common Table Expressions provide the kind of recursive functionality you need. Study the creation and use of the Level field in the article's sample query. It performs exactly the kind of incrementing you want to achieve, starting with 0 in the anchor query.

Working query based on provided sample:

WITH NavigationCTE  AS
(   
    SELECT navA.[Id], navA.ParentId, 0 AS depth_lvl
    FROM Navigation as navA

    UNION ALL

    SELECT navB.Id, navB.ParentId, depth_lvl + 1
    FROM Navigation AS navB
    JOIN NavigationCTE AS nav_cte_a
        --ON navB.ParentId = nav_cte_a.Id
        ON nav_cte_a.ParentId = navB.Id
)
SELECT Id, ParentId, coalesce(max(depth_lvl),0)
FROM NavigationCTE
GROUP BY Id, ParentId
ORDER BY Id, ParentId

edited Mar 18 '11 at 15:39

answered Mar 18 '11 at 13:03

Paul Sasik

79,492
20
149
189

This does not quite seem to determine the number of decendents, as it would seem the approach is the wrong way around. The OP wants to know how many Decendants there are for each entry, how many items under this item. – Adriaan Stander Mar 18 '11 at 13:15
Thanks. When I add level to the select and the recursion below the union, I get "Invalid column name 'Level'" where I increment + 1 to Level. – Mar 18 '11 at 13:16
@astander: That's right, it's not quite on the mark but I thought it's close enough, a foot in the door as it were, for Neil to start playing with CTEs recursive capabilities. – Paul Sasik Mar 18 '11 at 13:19
@Neil: Did you remember to add the Level field in your WITH clause at the very top? If you add the code you're playing with into the OP we can better help with simple syntactic issues such as this. – Paul Sasik Mar 18 '11 at 13:20
The second query of your UNION was not well-formed. I edited it in the OP. Please try that out. – Paul Sasik Mar 18 '11 at 13:49
I still get the same error, can you replace the DirecReports example with my navigationCTE? – Mar 18 '11 at 13:55
OK. Just added a query to my answer. I tried to clean it up and simplify as best I could but it's hard doing SQL "blind." Give it a go. – Paul Sasik Mar 18 '11 at 15:01
By the way, Level is a keywor din SQL Server. So i changed it to depth_lvl. You can also do square brackets around keywords if you like but that's not my thing. – Paul Sasik Mar 18 '11 at 15:03
Oops. Updated again. This one runs, at least. What is the output you're expecting exactly? – Paul Sasik Mar 18 '11 at 15:28
OK. Final update to the answer. This version actually gives you the result you need. Sorry for all the iteration. – Paul Sasik Mar 18 '11 at 15:40

Calculate number of descendants recursively

1 Answers1