-
Notifications
You must be signed in to change notification settings - Fork 3
/
Employee Hierarchy
46 lines (35 loc) · 1.68 KB
/
Employee Hierarchy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Employee Hierarchy
Table : employees
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1 | Boss | NULL |
| 3 | Alice | 3 |
| 2 | Bob | 1 |
| 4 | Daniel | 2 |
| 7 | Luis | 4 |
| 8 | Jhon | 3 |
| 9 | Angela | 8 |
| 77 | Robert | 1 |
+-------------+---------------+------------+
Here two kinds of base questions could be asked.
1) Print Employees under certain Manager. (Top to Bottom)
2) Print Managers of certain employee. (Bottom to Top)
Both kind of questions can be solved using join condition but we need to apply multiple joins & query would not be readable.
So to solve these types of hierarchical questions we can use RECURSIVE ctes.
1) Print Employees under certain Manager till level 3. (Top to Bottom)
Take details of certain manager as a base query & in the recursive query take direct employees of that manager
WITH RECURSIVE cte AS (
SELECT employee_id,employee_name,manager_id,1 AS level
FROM employees_1270
WHERE employee_id = 1
UNION
SELECT e.employee_id,e.employee_name,e.manager_id,level+1 AS level
FROM cte c
INNER JOIN employees_1270 e ON c.employee_id = e.manager_id
level < 4
)
SELECT DISTINCT employee_id,employee_name FROM cte WHERE employee_id <> 1;
2) Print Managers of certain employee. (Bottom to Top)
Take details of certain employee as a base query & in the recursive query take direct manager of that employee.
Just modify the above solution