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?