CTE Recursive Queries

Question

I have a table with records of employees that shows a relationship of employees and who they report to:

From_ID   position          TO_ID   position
----------------------------------------
1         Lowest_employee   3       employee
3         employee          4       employee
4         employee          5       BOSS
2         Lowest_employee   6       employee
6         employee          3       employee
10        Lowest_employee   50      BOSS2

I would like to show results that look like this, with the employee / boss IDs:

EmployeeID   BossID
--------------------
1            5
2            5
10           50

This means employees 1 and 2 report to ID 5 and employee 10 reports to another boss with ID 50.

I know I need to use CTE and Recursive Queries, but cannot understand how it can be done, I'm newer to CTE Recursive Queries.

I read this article but it doesn't make any sense to me MS link

Any help with the query required to achieve this would be useful.

Answer 1

You could try something like this?

DECLARE @Employees TABLE (
    EmployeeId INT,
    PositionName VARCHAR(50),
    ReportsToId INT);
INSERT INTO @Employees VALUES (1, 'Driver', 3);
INSERT INTO @Employees VALUES (3, 'Head of Driving Pool', 4);
INSERT INTO @Employees VALUES (4, 'Corporate Flunky', 5);
INSERT INTO @Employees VALUES (2, 'Window Cleaner', 6);
INSERT INTO @Employees VALUES (6, 'Head of Office Services', 3);
INSERT INTO @Employees VALUES (10, 'Minion', 50);
INSERT INTO @Employees VALUES (5, 'BOSS', NULL);
INSERT INTO @Employees VALUES (50, 'BOSS2', NULL);
WITH Employees AS (
    SELECT
        EmployeeId,
        1 AS [Level],
        EmployeeID AS [Path],
        ISNULL(ReportsToId, EmployeeId) AS ReportsToId
    FROM 
        @Employees
    WHERE 
        ReportsToId IS NULL
    UNION ALL
    SELECT
        e.EmployeeID,
        x.[Level] + 1 AS [Level],
        x.[Path] + e.EmployeeID AS [Path],
        x.ReportsToId
    FROM 
        @Employees e
        INNER JOIN Employees x ON x.EmployeeID = e.ReportsToId)
SELECT
    ec.EmployeeId,
    e.PositionName,
    ec.[Level],
    CASE WHEN ec.ReportsToId = ec.EmployeeId THEN NULL ELSE ec.ReportsToId END AS ReportsToId --Can't really report to yourself
FROM 
    Employees ec
    INNER JOIN @Employees e ON e.EmployeeId = ec.EmployeeId
ORDER BY 
    ec.[Path];

Answer 2

This includes setting up test data, however I think this is what you want:

Test Data:

DECLARE @Table TABLE
(
    From_ID int, 
    TO_ID int
)
INSERT INTO @Table VALUES(1,3)
INSERT INTO @Table VALUES(3,4)
INSERT INTO @Table VALUES(4,5)
INSERT INTO @Table VALUES(2,6)
INSERT INTO @Table VALUES(6,3)
INSERT INTO @Table VALUES(10,50)

Query to get answer:

;WITH Hierarchy (Employee, Superior, QueryLevel)
AS
(
    --root is all employees that have no subordinates
    SELECT  E.From_ID, E.TO_ID, 1
    FROM    @Table E
    LEFT
    JOIN    @Table S
            ON  S.TO_ID = E.From_ID
    WHERE   S.TO_ID IS NULL
    --recurse up tree to final superior
    UNION ALL
    SELECT  H.Employee, S.TO_ID, H.QueryLevel + 1
    FROM    Hierarchy H
    JOIN    @Table S
            ON  S.From_ID = H.Superior
)
SELECT Employee, Superior 
FROM
(
    SELECT  *, ROW_NUMBER() OVER(PARTITION BY Employee ORDER BY QueryLevel DESC) AS RowNumber 
    FROM Hierarchy
) H
WHERE RowNumber = 1

Essentially, this works by :

1) get all employees with no reportees (the root)

2) recurses up through the bosses, recording the 'level'

3) use over/partition to select only the 'final' boss

Answer 3

WITH    q (employee, boss) AS
        (
        SELECT  fromId, toId
        FROM    mytable
        WHERE   fromId NOT IN
                (
                SELECT  toId
                FROM    mytable
                )
        UNION ALL
        SELECT  employee, toId
        FROM    q
        JOIN    mytable t
        ON      t.fromId = boss
        )
SELECT  *
FROM    q
WHERE   boss NOT IN
        (
        SELECT  fromId
        FROM    mytable
        )