Gup3rSuR4c Gup3rSuR4c - 2 years ago 99
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download