CODEWITHSUNDEEP
sqlsql-servercommon-table-expressionrecursive-query
Zac Truelove
Zac Truelove

Reputation: 55

SQL Server Recursive CTE - Why this behavior?

Given these tables...

CREATE TABLE tblEmployees (
    EmployeeID  SMALLINT,
    ReportsTo   SMALLINT,
    IsBigBoss   BIT);

CREATE TABLE tblTargetEmployees (
    EmployeeID SMALLINT);

INSERT INTO tblEmployees VALUES
(1,NULL,NULL),
(2,1,1),
(3,1,1),
(4,1,1),
(5,1,1),
(6,2,0),
(7,6,0),
(8,6,0),
(9,3,0),
(10,4,0),
(11,10,0),
(12,10,0),
(13,5,0),
(14,2,0),
(15,10,0);

INSERT INTO tblTargetEmployees VALUES
(8),
(9),
(10),
(11),
(12),
(14);

And this query...

WITH cte AS (
    SELECT e.EmployeeID, BigBossID=e.EmployeeID, e.ReportsTo
        FROM tblEmployees e
        WHERE e.IsBigBoss=1

    UNION all

    SELECT e.EmployeeID, cte.BigBossID, e.ReportsTo
        FROM tblEmployees e
            JOIN cte ON e.ReportsTo=cte.EmployeeID
)

SELECT *
    FROM cte
    WHERE EXISTS (SELECT * FROM tblTargetEmployees te WHERE te.EmployeeID=cte.EmployeeID)
    ORDER by EmployeeID

I get the results expected. All six employees from my target table are returned. However, if I move the filter into the cte instead, I drop an employee (#8).

WITH cte AS (
    SELECT e.EmployeeID, BigBossID=e.EmployeeID, e.ReportsTo
        FROM tblEmployees e
        WHERE e.IsBigBoss=1

    UNION all

    SELECT e.EmployeeID, cte.BigBossID, e.ReportsTo
        FROM tblEmployees e
            JOIN cte ON e.ReportsTo=cte.EmployeeID
        WHERE EXISTS (SELECT * FROM tblTargetEmployees te WHERE te.EmployeeID=e.EmployeeID)
)

SELECT *
    FROM cte
    ORDER by EmployeeID

Now, I understand why my extra "Big Boss" rows come across when I move the filter into the cte, but I'm having a hard time wrapping my head around why employeeID 8 gets filtered out.

Any assistance with helping me wrap my simple mind around this behavior is much appreciated.

Upvotes: 2

Views: 88

Answers (1)

Matt
Matt

Reputation: 14341

Because EmployeeId 6 doesn't Exist in the tblTargetEmployees so when it gets to the recursion and starts adding employee id 6 it looks up if it exists in the tblTargetEmployees table. It doesn't so that line of recursion stops if and never gets to employee id 8.

So to use the data

  • 2 is bigboss and is represented in anchor table
  • 6 is direct boss but doesn't exist so it doesn't call the recursion again after that.
  • 8 is waiting to be found.....

move your WHERE statement outsde of the cte and you will see the results you want

;WITH cte AS (
    SELECT e.EmployeeID, BigBossID=e.EmployeeID, e.ReportsTo
        FROM tblEmployees e
        WHERE e.IsBigBoss=1

    UNION all

    SELECT e.EmployeeID, cte.BigBossID, e.ReportsTo
        FROM tblEmployees e
            JOIN cte ON e.ReportsTo=cte.EmployeeID
)

SELECT *
    FROM cte e
    WHERE EXISTS (SELECT * FROM tblTargetEmployees te WHERE te.EmployeeID=e.EmployeeID)
    ORDER by EmployeeID

Upvotes: 2

Related Questions