Recursive CTE (T-SQL) Returns Un-expected Result

Question

I've been staring at this code for WAY too long, trying to figure out why my final query returns unexpected results.

Any help would be much appreciated. Thanks in advance.

Given the following code (running on SQL Server 2008 R2):

USE tempdb;

DECLARE @emp--loyee
TABLE (
    EmployeeID int NOT NULL
    ,EmployeeName nvarchar(50) NOT NULL
    PRIMARY KEY(EmployeeID)
)

INSERT INTO @emp
SELECT 1,'Fred'
UNION
SELECT 2,'Mary'
UNION
SELECT 3,'Joe'
UNION
SELECT 4,'Bill'

DECLARE @grp TABLE (
    GroupID int NOT NULL
    ,GroupName nvarchar(50)
    PRIMARY KEY(GroupID)
)

INSERT INTO @grp
SELECT 1,'Group 1'
UNION
SELECT 2,'Group 2'
UNION
SELECT 3,'Group 3'


DECLARE @empgrp TABLE (
    EmployeeID int NOT NULL
    ,GroupID int NOT NULL
    PRIMARY KEY (EmployeeID,GroupID)
)

INSERT INTO @empgrp
SELECT 1,1
UNION
SELECT 2,1
UNION
SELECT 3,1
UNION
SELECT 4,2

DECLARE @grpgrp TABLE (
    GroupID int NOT NULL
    ,ParentGroupID int
    ,UNIQUE CLUSTERED(GroupID,ParentGroupID)
)

INSERT INTO @grpgrp
SELECT 1,2
UNION
SELECT 2,3;


WITH AllEmpGroups (EmployeeID,GroupID,RootGroupID)
AS
(
    SELECT CAST(NULL as int) as EmployeeID,pgrp.GroupID,pgrp.ParentGroupID
    FROM @grpgrp pgrp LEFT JOIN @grpgrp ggrp
    ON pgrp.ParentGroupID = ggrp.GroupID
    UNION ALL
    SELECT e.EmployeeID,eg.GroupID,aeg.RootGroupID
    FROM @emp e JOIN @empgrp eg
    ON e.EmployeeID = eg.EmployeeID
    JOIN @grpgrp ggrp
    ON eg.GroupID = ggrp.GroupID
    JOIN AllEmpGroups aeg
    ON aeg.GroupID = ggrp.ParentGroupID
)

SELECT EmployeeID,GroupID,RootGroupID
FROM AllEmpGroups

What I get is:

+------------+---------+-------------+
| EmployeeID | GroupID | RootGroupID |
+------------+---------+-------------+
| NULL       |       1 |           2 |
| NULL       |       2 |           3 |
| 1          |       1 |           3 |
| 2          |       1 |           3 |
| 3          |       1 |           3 |
+------------+---------+-------------+

What I would expect/want to get is this:

+------------+---------+-------------+
| EmployeeID | GroupID | RootGroupID |
+------------+---------+-------------+
| NULL       |       1 |           2 |
| NULL       |       2 |           3 |
| 4          |       2 |           3 |
| 1          |       1 |           3 |
| 2          |       1 |           3 |
| 3          |       1 |           3 |
+------------+---------+-------------+

Bottom line, I want the full recursive stack of all employees beneath a given root group(s), with the root group id on every row.

What am I missing?

Answer 1

First:

1. You need a row for the root node in @grpgrp with values 3, null
2. The anchor (part before the union all) of your recursive cte needs to be the root node (3, null) for ancestor first recursion.

...

INSERT INTO @grpgrp
SELECT 1,2
UNION all
SELECT 2,3
UNION all
select 3, null;

WITH AllEmpGroups (EmployeeID,GroupID,RootGroupID)
AS
(
    SELECT CAST(NULL as int) as EmployeeID,pgrp.GroupID, ParentGroupID = pgrp.GroupID
    FROM @grpgrp pgrp LEFT JOIN @grpgrp ggrp
      ON pgrp.ParentGroupID = ggrp.GroupID
    where pgrp.ParentGroupId is null
    UNION ALL
    SELECT e.EmployeeID,eg.GroupID,aeg.RootGroupID
    FROM @emp e JOIN @empgrp eg
    ON e.EmployeeID = eg.EmployeeID
    JOIN @grpgrp ggrp
    ON eg.GroupID = ggrp.GroupID
    JOIN AllEmpGroups aeg
    ON aeg.GroupID = ggrp.ParentGroupID
)

SELECT EmployeeID,GroupID,RootGroupID
FROM AllEmpGroups

rextester demo: http://rextester.com/CBWY80387

returns:

+------------+---------+-------------+
| EmployeeID | GroupID | RootGroupID |
+------------+---------+-------------+
| NULL       |       3 |           3 |
| 4          |       2 |           3 |
| 1          |       1 |           3 |
| 2          |       1 |           3 |
| 3          |       1 |           3 |
+------------+---------+-------------+

---

Beyond that, I would build the groups hierarchy first, then join the employees like so:

WITH AllEmpGroups (GroupID,ParentGroupID,RootGroupID)
AS
(
    SELECT pgrp.GroupID, pgrp.ParentGroupID, RootGroupId = GroupID
    FROM @grpgrp pgrp 
    where pgrp.ParentGroupId is null
    UNION ALL
    SELECT ggrp.GroupID,ggrp.ParentGroupID,aeg.RootGroupID
    FROM  @grpgrp ggrp
    inner JOIN AllEmpGroups aeg
        ON aeg.GroupID = ggrp.ParentGroupID

)
SELECT eg.EmployeeID,aeg.*
FROM AllEmpGroups aeg
    left JOIN @empgrp eg 
        ON eg.GroupID = aeg.GroupID

rextester demo: http://rextester.com/FAK76354

returns:

+------------+---------+---------------+-------------+
| EmployeeID | GroupID | ParentGroupID | RootGroupID |
+------------+---------+---------------+-------------+
| NULL       |       3 | NULL          |           3 |
| 4          |       2 | 3             |           3 |
| 1          |       1 | 2             |           3 |
| 2          |       1 | 2             |           3 |
| 3          |       1 | 2             |           3 |
+------------+---------+---------------+-------------+

Answer 2

Start with

WITH AllGroups (RootGroupID,GroupID,ParentGroupID, level)
AS
(
    SELECT GroupID RootGroupID, GroupID, Cast(NULL as int) ParentGroupID, 0 level
    FROM @grp g
    WHERE NOT EXISTS (SELECT 1 FROM @grpgrp gg WHERE gg.GroupID = g.GroupID)

    UNION ALL
    SELECT ag.RootGroupID, gg.GroupID, gg.ParentGroupID, level+1
    FROM @grpgrp gg
    JOIN AllGroups ag
    ON ag.GroupID = gg.ParentGroupID
)


SELECT EmployeeID, ag.GroupID, ParentGroupID, RootGroupID
FROM AllGroups ag
LEFT JOIN  @empgrp eg ON eg.GroupID = ag.GroupID
ORDER BY RootGroupID, level, ParentGroupID, GroupID;

Not sure why you need the row:

| NULL       |       2 |           3 |