Discussion Locked

recursive query

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is it possible to make a recursive query?
It's about supervision levels, to select employees the works under supervision of certain person and inturn select employees that work under supervision of first query ...etc , till i reach the last level.

5 replies

by olivier_de-polignac

A simple way of doing that is storing the "management path" of each employee.

For exemple, you can have the following table:

Employee_Id
Employee_Name
Employee_Path

Let us assume we have the following datas:

1 - 'John' - '/'
2 - 'Peter' - '/1/'
3 - 'Sandra' - '/1/'
4 - 'Elsbeth' - '/1/3/'

You understand that John is the "top boss", supervizing Peter and Sandra, and Sandra supervizes Eslbeth.

The beauty of the "Management Path" is that you can directly select all employees"below" someone by doing:

SELECT * FROM tab_Employee WHERE Employee_Path LIKE '/1/%'

This will returns all employees working below
Peter.

Hope this helps.

Olivier

by steved

Here's a solution that uses a temporary table to store all the relationships recursively. It's taken from the excellent book "The Guru's Guide to Transact-SQL" by Ken Henderson. You can probably adapt it to your own purposes.

select seq=identity(int), chardepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2
on o1.employee = o2.supervisor

while (@@rowcount > 0)
Begin
insert #org_chart (chartdepth, employee, supervisor)
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 inner join #org_chart o2
on o1.employee=o2.supervisor
where o1.chartdepth = (select max(chartdepth) from #org_chart)
and o1.supervisor <> o1.employee
End

select s.employee_name, supervises='supervises', e.employee_name
from #org_chart o inner join staff s
on o.supervisor = s.employee
inner join staff e on o.employee = e.employee
where o.supervisor <> o.employee
order by seq

I hope this helps.

Steve Diamond

This discussion is closed to new replies.