CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Saurav Kundu
Saurav Kundu

Reputation: 9

I need all the employee under a supervisor

i have a below described situation

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

I need all the employees under supervisor ID 1

Here EmployeeID 3 is under 1 i need 4 is is also under 3

Following output is required.

EmpID   Name    SupervisorID
3       C       1
4       D       3

Upvotes: 0

Views: 129

Answers (2)

Ben Thul
Ben Thul

Reputation: 32707

Nobody expects the Spanish Inquisition a HierarchyId. These days, whenever I see an org structure (like the one you have here), I reach for the HierarchyId datatype. In essence, it allows you to answer questions like "which value(s) are 'under' this one?" and "which value(s) does this one belong to?". Here's how I'd 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/o a supervisor

   union all

   select e.EmployeeId, e.SupervisorId, h.h + '/' + cast(EmployeeId as varchar) + '/'
   from dbo.Employee as e
   join h
      on e.SupervisorId = h.SupervisorId
)
update e
set OrgStructure = h.h
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 lifting is done, actually answering 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're not calculating the hierarchy on the fly every time you need to answer this type of question. You do it once and you're done.

Upvotes: 0

AK47
AK47

Reputation: 3797

You need to Use Recursive CTE for this.Try this,

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

Fiddle Demo Here

Please take a look at this question also, it is same like your question. Sql server CTE and recursion example

Upvotes: 2

Related Questions