Recursive query SQL Server not working as expected

Question

thanks in advance for you help. I'm still quite new to MS SQL db but I was wondering why my recursive query for MSSQL below does not return the value i'm expecting. I've done my research and at the bottom is the code I came up with. Lets say I have the following table...

CategoryID    ParentID    SomeName
1             0           hmm
2             0           err
3             0           woo
4             3           ppp
5             4           ttt

I'm expecting the query below to return 3 4 5. I basically wanted to get the list of category id's heirarchy below it self inclusive based on the category id I pass in the recursive query. Thanks for you assistance.

GO
WITH RecursiveQuery (CategoryID)
AS
(
-- Anchor member definition
    SELECT a.CategoryID 
    FROM [SomeDB].[dbo].[SomeTable] AS a
    WHERE a.ParentID = CategoryID 
    UNION ALL
-- Recursive member definition
    SELECT b.CategoryID
    FROM [SomeDB].[dbo].[SomeTable] AS b
    INNER JOIN RecursiveQuery AS d
        ON d.CategoryID = b.ParentID
)
-- Statement that executes the CTE
SELECT o.CategoryID
FROM [SomeDB].[dbo].[SomeTable] AS o
INNER JOIN RecursiveQuery AS d
    ON d.CategoryID = 3
GO

And I guess final select might be from RecursiveQuery. d.theID will not produce crosscutting value 3 it will be the list of all ids. So filter = 3 will give you one record. — IVNSTN
– IVNSTN, Commented Jul 8, 2016 at 9:27
As @IvanStarostin says, your anchor member definition isn't anchoring to a record. The anchor should be one record that acts as your starting point. — Jonathon Ogden
– Jonathon Ogden, Commented Jul 8, 2016 at 9:27
ok thanks I thought anchor was the terminating condition ok my mistake will try it out... — debonaire
– debonaire, Commented Jul 8, 2016 at 9:34

debonaire · Accepted Answer · 2016-07-11 06:58:49Z

3

If you want tree from specific root:

DECLARE @rootCatID int = 3

;WITH LessonsTree (CatID)
AS
(
    SELECT a.CategoryID
    FROM [EducationDatabase].[dbo].[LessonCategory] AS a
    WHERE a.CategoryID = @rootCatID ---<<<

    UNION ALL

    SELECT b.CategoryID
    FROM LessonsTree as t
    INNER JOIN [EducationDatabase].[dbo].[LessonCategory] AS b
        ON b.ParentID = t.CatID
)
SELECT o.*
FROM LessonsTree t
INNER JOIN [EducationDatabase].[dbo].[LessonCategory] AS o
    ON o.CategoryID = t.CatID

edited Jul 11, 2016 at 6:58

debonaire

3293 silver badges11 bronze badges

answered Jul 8, 2016 at 9:37

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IVNSTN Over a year ago

@debonaire you should mark it as the answer if it is.

debonaire Over a year ago

thanks will do now. can you approve the edit of the variable name thanks so much

Me.Name · Accepted Answer · 2016-07-08 09:51:51Z

1

As stated in the comments, the anchor isn't restricted. Easiest solution is to add the criterium in the anchor

with RecursiveQuery (theID)
AS
(
    SELECT a.ParentID --root id=parentid to include it and to prevent an extra trip to LessonCategory afterwards
    FROM  [LessonCategory] AS a
    WHERE a.ParentID = 3 --restriction here
    UNION ALL
    SELECT b.CategoryID
    FROM [LessonCategory] AS b
    INNER JOIN RecursiveQuery AS d
        ON d.theID = b.ParentID
)
SELECT* from RecursiveQuery

Another option is to have the recursive query be general (no restricted anchor) and have it keep the rootid as well. Then the query on the cte can restrict on the rootid (the first option is probably better, this second one is mainly suitable if you are created some sort of root-view)

with RecursiveQuery 
AS
(
    SELECT a.ParentID theID, a.ParentID RootID
    FROM  [LessonCategory] AS a    
    UNION ALL
    SELECT b.CategoryID, d.RootID
    FROM [LessonCategory] AS b
    INNER JOIN RecursiveQuery AS d
        ON d.theID = b.ParentID
)
SELECT theID from RecursiveQuery where RootID = 3

answered Jul 8, 2016 at 9:51

Me.Name

12.6k3 gold badges34 silver badges48 bronze badges

1 Comment

debonaire Over a year ago

thanks so much for the suggestions. Will take that into account. Cheers!

Collectives™ on Stack Overflow

Recursive query SQL Server not working as expected

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related