1 drop table if exists employee;
2
3 create table employee
4 (
5 emp_id smallint unsigned not null auto_increment primary key,
6 name varchar(32) not null,
7 boss_id smallint unsigned null,
8 key boss_id_idx(boss_id)
9 )engine = innodb;
10
11
12 insert into employee (name, boss_id) values
13 ('foo',null),
14 ('ali later',1), ('megan fox',1),
15 ('jessica alba',2), ('eva longoria',2),
16 ('keira knightley',3), ('liv tyler',3),
17 ('sophie marceau',5);
18
19
20 delimiter ;
21
22 drop procedure if exists employee_hier;
23
24 delimiter #
25
26 create procedure employee_hier
27 (
28 in p_emp_id smallint unsigned
29 )
30 begin
31
32 declare p_done tinyint unsigned default(0);
33 declare p_depth smallint unsigned default(0);
34
35 create temporary table hier(
36 boss_id smallint unsigned,
37 emp_id smallint unsigned,
38 depth smallint unsigned
39 )engine = memory;
40
41 insert into hier values (null, p_emp_id, p_depth);
42
43 /* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */
44
45 create temporary table emps engine=memory select * from hier;
46
47 while 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 insert into emps select * from hier where depth = p_depth;
58
59 else
60 set p_done = 1;
61 end if;
62
63 end while;
64
65 select
66 e.emp_id,
67 e.name as emp_name,
68 b.emp_id as boss_emp_id,
69 b.name as boss_name,
70 hier.depth
71 from
72 hier
73 inner join employee e on hier.emp_id = e.emp_id
74 inner join employee b on hier.boss_id = b.emp_id;
75
76 drop temporary table if exists hier;
77 drop temporary table if exists emps;
78
79 end #
80
81 delimiter ;
82
83 /*
84
85 select * from employee;
86
87 emp_id name boss_id
88 ====== ==== =======
89 1 foo null
90 2 ali later 1
91 3 megan fox 1
92 4 jessica alba 2
93 5 eva longoria 2
94 6 keira knightley 3
95 7 liv tyler 3
96 8 sophie marceau 5
97
98 call employee_hier(1);
99
100 emp_id emp_name boss_emp_id boss_name depth
101 ====== ======== =========== ========= =====
102 2 ali later 1 foo 1
103 3 megan fox 1 foo 1
104 4 jessica alba 2 ali later 2
105 5 eva longoria 2 ali later 2
106 6 keira knightley 3 megan fox 2
107 7 liv tyler 3 megan fox 2
108 8 sophie marceau 5 eva longoria 3
109
110 call employee_hier(3);
111
112 emp_id emp_name boss_emp_id boss_name depth
113 ====== ======== =========== ========= =====
114 6 keira knightley 3 megan fox 1
115 7 liv tyler 3 megan fox 1
116 */
