Reiterating problem. This works:
--DDL for creating and populating staff table
create table staff(employee int primary key, employee_name varchar(10),
supervisor int null references staff (employee))
insert staff
values
(1, 'groucho', 1),
(2, 'chico', 1),
(3, 'harpo', 2),
(4, 'zeppo', 2),
(5, 'moe', 1),
(6, 'larry', 5),
(7, 'curly', 5),
(8, 'shemp', 5),
(9, 'joe', 8),
(10, 'curly joe', 9);
--query #1
--establishes first 10 rows of table, with first level of chart depth
select chartdepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2 on (o1.employee=o1.supervisor)
--query#2
--following the execution of query #1 run the below query about 4-5 times (f5 x 5), #org_chart will be populated with about 5 levels of chart depth
insert into #org_chart
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)
---verify population of #org_chart with this query
Select * from #org_chart;
---query #3 returns boss -> subordinate relationships
select s.employee_name, supervises='supervises', e.employee_name
from #org_chart o join staff s on (o.supervisor=s.employee)
inner join staff e on (o.employee=e.employee)
where o.supervisor<>o.employee
HOWEVER, if you want to spare yourself hitting f5 five times, you are supposed to be able to run this query instead:
while (@@rowcount > 0)
begin
insert #org_chart (chartdepth, employee, supervisor)
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)
and o1.supervisor<>o1.employee
end
But this while query doesn't work. What corrections need to be made to while query to do the job?
--Quote me