I need an entire employee under supervision

Question

i has the situation described below.

EmpID Name SupervisorID 1 A 9 2 B 8 3 C 1 4 D 3 

I need all employees under administrator ID 1

Here EmployeeID 3 is under 1, I need 4, also under 3

Next exit required.

 EmpID Name SupervisorID 3 C 1 4 D 3 

Answer 1

For this you need to use a recursive CTE. Try it,

 With CTE as ( select EmpID,Name,SupervisorID from Emp where SupervisorID =1 Union All select a.EmpID,a.Name,a.SupervisorID from Emp as a inner join CTE b on a.SupervisorID= b.EmpID ) select * from CTE 

Screenshot Demo here

Please also look at this question, it will be the same as your question. Example CTK server and server recursion <

Answer 2

No one expects the Spanish Inquisition of hierarchies. These days, when I see an org structure (like the one you have here), I refer to the HierarchyId data type. Essentially, it allows you to answer questions such as “what value (s) is under this”? And “what value does this belong to?” Here's how I implement it:

 alter table dbo.Employee add OrgStructure HierarchyId null; with h as ( select EmployeeId, SupervisorId, '/' + cast(EmployeeId as varchar) + '/' as h from dbo.Employee as e where e.SupervisorId is null --employees w/oa supervisor union all select e.EmployeeId, e.SupervisorId, hh + '/' + cast(EmployeeId as varchar) + '/' from dbo.Employee as e join h on e.SupervisorId = h.SupervisorId ) update e set OrgStructure = hh from dbo.Employee as e join h on e.EmployeeId = h.EmployeeId; create index [IX_Employee_OrgStructure] on dbo.Employee (OrgStructure) 

Now that the heavy lift is done, in fact the answer to your problem is trivial:

 select * from dbo.Employee as supervisor join dbo.Employee as reports on reports.OrgStructure.IsDescendantOf(supervisor.OrgStructure) where supervisor.EmployeeId = 1 

The advantage that I see is that you do not calculate the hierarchy on the fly every time you need to answer this question. You do it once, and you're done.