sCuUAKV8

1drop table if exists employee;
2
3create table employee
4(
5emp_id int unsigned not null auto_increment primary key,
6name varchar(32) not null,
7boss_id int unsigned null
8);
9
10insert into employee (name, boss_id) values
11('foo',null), 
12('ali later',1),
13('megan fox',1),
14('jessica alba',2),
15('eva longoria',2),
16('keira knightley',3),
17('liv tyler',3),
18('sophie marceau',5);
19
20
21delimiter ;
22
23drop procedure if exists employee_hier;
24
25delimiter #
26
27create procedure employee_hier
28(
29in p_emp_id int unsigned
30)
31begin
32
33declare p_done tinyint unsigned default(0);
34declare p_depth tinyint unsigned default(0);
35
36create temporary table hier
37(
38 boss_id int unsigned,
39 emp_id int unsigned,
40 depth tinyint unsigned
41)engine = memory;
42
43insert into hier values (null, p_emp_id, p_depth);
44
45create temporary table emps engine=memory select * from hier;
46
47while p_done <> 1	do
48 
49	if exists( select 1 from employee e inner join hier on e.boss_id = hier.emp_id and hier.depth = p_depth) then
50		
51		insert into hier select e.boss_id, e.emp_id, p_depth + 1 
52			from employee e inner join emps on e.boss_id = emps.emp_id and emps.depth = p_depth;
53
54		set p_depth = p_depth + 1;			
55		
56		truncate table emps;
57		
58		insert into emps select * from hier where depth = p_depth;
59		
60	else
61		set p_done = 1;
62	end if;
63	
64end while;
65
66select 
67 e.emp_id,
68 e.name as emp_name,
69 b.emp_id as boss_emp_id,
70 b.name as boss_name,
71 hier.depth
72from 
73 hier
74inner join employee e on hier.emp_id = e.emp_id
75inner join employee b on hier.boss_id = b.emp_id;
76		
77drop temporary table if exists hier;
78drop temporary table if exists emps;
79
80end #
81
82delimiter ;
83
84/*
85
86select * from employee;
87
88emp_id	name		boss_id
89======	====		=======
901	foo		null
912	ali later	1
923	megan fox	1
934	jessica alba	2
945	eva longoria	2
956	keira knightley	3
967	liv tyler	3
978	sophie marceau	5
98
99call employee_hier(1);
100
101emp_id	emp_name	boss_emp_id	boss_name	depth
102======	========	===========	=========	=====
1032	ali later	1		foo		1
1043	megan fox	1		foo		1
1054	jessica alba	2		ali later	2
1065	eva longoria	2		ali later	2
1076	keira knightley	3		megan fox	2
1087	liv tyler	3		megan fox	2
1098	sophie marceau	5		eva longoria	3
110
111call employee_hier(3);
112
113emp_id	emp_name	boss_emp_id	boss_name	depth
114======	========	===========	=========	=====
1156	keira knightley	3		megan fox	1
1167	liv tyler	3		megan fox	1
117*/