Gup3rSuR4c Gup3rSuR4c - 7 months ago 11
SQL Question

SQL recursive CTE returns more results than expected

Using SQL I'm trying to get a list of ids of a user's descendants. So, say I have:

Id | ManagerId | Name
---|-----------|------
4 | NULL | James Smith
7 | 4 | John Doe (1)
8 | 7 | John Doe (2)
9 | 8 | John Doe (3)
10 | 8 | John Doe (4)


And I want to get back
4, 7, 8, 9, 10
. Looking around online the recommended approach is to create a recursive CTE, which I've done as:

WITH UsersCTE AS (
SELECT Id,
ManagerId
FROM Users
WHERE IsActive = 1
UNION ALL
SELECT A.Id,
UsersCTE.Id
FROM UsersCTE
INNER JOIN Users AS A
ON A.ManagerId = UsersCTE.ManagerId
)

SELECT *
FROM UsersCTE;


Which kind of works. It starts out by getting the ids that I want, and then just goes off and starts getting ids in all kind of manners. I'll confess that I have no idea how it works, but it's not giving me the result I want. Instead it's giving me this:

enter image description here

How can I get it to give me just the ids I want. For reference, I need to get this list so I can then query back the db and get a list of orders for the current users and any descendants they manage. I am using EF6 for the data access and was planning on making this CTE into a view and querying it appropriately, but I'm open to better recommendations.

Answer

Rewrite as follows:

WITH UsersCTE AS (
    SELECT  Id,
            ManagerId
    FROM    Users
    WHERE   IsActive = 1 and ManagerID is null
    UNION ALL
    SELECT  A.Id,
            UsersCTE.Id
    FROM    UsersCTE
            INNER JOIN Users AS A
                ON A.ManagerId = UsersCTE.Id
)

SELECT  *
FROM    UsersCTE;

The null filter in the first part anchors your CTE i.e. gives it a base case of employees with no managers. The other change is in the join condition of the second part. The updated condition matches manager ID to employee ID.

Demo

Comments