Cfrgtkky

I have an Employee Table with an EmployeeId, ManagerId and a Name field.

The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).

I found this link which helped to get the base of the code but I do not manage to make it work for my case

DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)

INSERT @EmployeeTable VALUES (1,'Jerome', NULL )  -- tree is as follows:

INSERT @EmployeeTable VALUES (2,'Joe'   ,1)     --                      1-Jerome

INSERT @EmployeeTable VALUES (3,'Paul'  ,2)     --                     /        

INSERT @EmployeeTable VALUES (4,'Jack'  ,3)     --              2-Joe               9-Bill

INSERT @EmployeeTable VALUES (5,'Daniel',3)     --            /                         

INSERT @EmployeeTable VALUES (6,'David' ,2)     --     3-Paul          6-David       10-Sam

INSERT @EmployeeTable VALUES (7,'Ian'   ,6)     --    /                  /    

INSERT @EmployeeTable VALUES (8,'Helen' ,6)     -- 4-Jack  5-Daniel   7-Ian    8-Helen

INSERT @EmployeeTable VALUES (9,'Bill ' ,1)     --

INSERT @EmployeeTable VALUES (10,'Sam'  ,9)     --



DECLARE @employeeId int = 3



;WITH StaffTree AS

(

    SELECT 

        c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]

        FROM @EmployeeTable c

        LEFT OUTER JOIN @EmployeeTable  cc ON c.managerId=cc.EmployeeId

        WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)

    UNION ALL

        SELECT 

            s.[EmployeeId], s.[name], s.managerId, t.[Level]+1

        FROM StaffTree t

            INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId

        WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1

)

SELECT * FROM StaffTree

In case you select the employee 3 hierarchy, the result should be:

EmployeeId | Name    | ManagerId 

1          | Jerome  | NULL

2          | Joe     | 1

3          | Paul    | 2

Swapping the columns in the inner join in the recursive part is a way to go about this.

Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId

I deleted some parts that did not seem like they were needed.

DECLARE @employeeId int = 3



;WITH StaffTree AS

(

    SELECT 

        c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]

        FROM @EmployeeTable c

        WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)

    UNION ALL

        SELECT 

            s.[EmployeeId], s.[name], s.managerId, t.[Level]+1

        FROM StaffTree t

        INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId

)

SELECT EmployeeId,

       name,

       managerId 

FROM StaffTree

ORDER BY managerId asc;

Result

EmployeeId  name    managerId

1           Jerome  NULL

2           Joe     1

3           Paul    2

It looks like you had a tiny error when copying the example that you linked to -

c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]

should be

c.[EmployeeId], c.[name], c.managerId, 1 AS [Level]

Once I make that change, your code functions the same way as the example.

This Link will help you a lot in learning Recursive CTEs with excellent and useful examples : https://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child.