3

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
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
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.