--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 does not work for me
--it is supposed to populate #org_chart with remaining levels of chartdepth. 
--A while loop repeats the insert as many times as necessary to process all levels in staff table

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

--if following the execution of query #1 you run the below query about 4-5 times, it accomplishes what query #2 is supposed to be able to 
--accomplish with a While loop

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)

					
SELECT DISTINCT
		o1.chartdepth + 1 ,
		o1.employee ,
		o1.supervisor
	FROM
		#org_chart o1
	JOIN 
		#org_chart o2
			ON ( o1.employee = o2.supervisor )
	WHERE
		o1.chartdepth = (
					 SELECT
						MAX(#org_chart.chartdepth)
					FROM
						#org_chart
					)
					AND 
						o1.supervisor <> o1.employee

--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

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

select seq=identity(int), chartdepth=1, employee=o2.employee,
supervisor=o1.employee
into #org_chart
from staff o1 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 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 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