I'm hoping someone can help out with this. It seems like it should be easy, yet I keep banging my head against it to no avail. In short, I've got a table of employees. There's a number of fields, but the only relevant ones are:
ID int
SupervisorID int
LastName varchar (25)
What I'm trying to get is a list, by employee, of everyone under him in the hierarchy - not just one level, but all the way down. So, if the table has
1 Null Smith
2 1 Jones
3 1 Pink
4 2 White
5 3 Black
6 4 Green
I'd want to return something along the lines of:
LastName Supervises
Smith Jones
Smith Pink
Smith White
Smith Black
Smith Green
Jones White
Jones Green
Pink Black
White Green
Here's the code I've been tinkering with - in it's current state, it gets me one level down in the hierarchy.
WITH
EmployeeCTE AS (SELECT ID, LastName , SupervisorID FROM Employee_EmployeeDatabase WHERE SupervisorID is null UNION ALL SELECT E.ID, E.LastName, E.SupervisorID FROM Employee_EmployeeDatabase E JOINEmployeeCTE x
ON x.ID = e.SupervisorID) SELECT ID, SupervisorID, LastName FROM EmployeeCTE