Trace a Hierarchy in a Table

Question

Hi

I have an "Employee" table with an "EmployeeID" column and a column representing Employee's Boss (BossID) which in turn is an employee in the "Employee" table. How can I trace the hierarchy from a given "EmployeeID" to the top most Boss. I do not want a self join approach in this, also I am using SQL Server 2005.

Thank you

Manu

Answer 1

You have to use some sort of self join basically with the table structure you describe but can use a recursive CTE for this to handle arbitrary depths of hierarchy if that was the concern?

WITH cte AS
(
SELECT EmployeeID, BossId
FROM Employee where EmployeeID = @EmployeeID
UNION ALL
SELECT e.EmployeeID, e.BossId
FROM Employee e JOIN cte ON cte.BossId = e.EmployeeID
)
SELECT EmployeeID 
FROM cte