Finding an organization's employee hierarchy using SQL typically involves working with a table that stores information about employees and their relationships within the organization. The table usually includes columns like `employee_id`, `manager_id`, and other relevant information. Here's a general approach using a common scenario:
Assuming you have a table named `employees` with the columns `employee_id` and `manager_id`, where `manager_id` refers to the `employee_id` of the manager:
;with RECURSIVEEmpH as
(
select e.EmployeeID,e.Name,e.ManagerID, 1 as depth from dbo.Employee e where e.ManagerID is null
union all
select e.EmployeeID,e.Name,e.ManagerID,eh.depth+1 from dbo.Employee e join RECURSIVEEmpH eh on e.ManagerID=eh.EmployeeId
)
select * from RECURSIVEEmpH
EmployeeID Name ManagerID depth
1 Mike 3 2
2 David 3 2
3 Roger NULL 1
4 Marry 2 3
5 Joseph 2 3
7 Ben 2 3
