query for hierarchy

Question

hi all,

my employee table is as follows:

empID orgLevel supervisorID
1222 0 4567
1233 0 4567
4567 1 8901
5678 1 8901
8901 2 9012

and so on.

I am trying to write a query that would give me a result like:

empIDChain empID orgLevel supervisorID

1222 1222 0 4567
1222 4567 1 8901
1222 8901 2 9012
1222 9012 3 null
1233 1233 0 4567
1233 4567 1 8901
1233 8901 2 9012
1233 9012 3 null
4567 4567 1 8901
4567 8901 2 9012
4567 9012 3 null
......
8901 8901 2 9012
8901 9012 3 null
9012 9012 3 null

So, from a table that has rows showing an employee id and its supervisor ID, I need a table showing all the rows for each employee and the full chain of command up to the last level.

Thank you very much,

elsvieta

Answer 1

I have added the following sample query

WITH EmployeeHierarchy AS (
    SELECT empID AS empIDChain, empID, orgLevel, supervisorID
    FROM EmployeeTable 
    WHERE supervisorID IS NOT NULL
    UNION ALL
    SELECT E.empIDChain, ET.empID, ET.orgLevel, ET.supervisorID
    FROM EmployeeHierarchy E
    JOIN EmployeeTable ET ON E.supervisorID = ET.empID
)
SELECT * FROM EmployeeHierarchy;