SQL get all children

user_10449669 0
Viewed 91

So I have hierarchical data that is organized in departments, with each department having a parent department except for the top one. I now want to write a SELECT statement to select this hierarchical structure in a cumulative way. That means that for every level in the hierarchy I want to see all the entries that are children of that.

For example, if I have the following tables: Departments

ID  PARENT_ID

1   null
2   1
3   1
4   2
5   2
6   3
7   3

Employees

ID  DEPT
1   2
2   2
3   3
4   4
5   5
6   6
7   7
8   2
9   3
10  4
11  5
12  6
13  7
14  2
15  3
16  4
17  5

I would like to have something like the following result:

ID_E ROOT DEPT
1    null 1
1    1    2
2    null 1
2    1    2
3    null 1
3    1    3
4    null 1
4    1    2
4    2    4
5    null 1
...

I have looked around and fiddled a bit but just cannot get it to work.

I thought this would do the trick but it gives weird results (meaning many duplicate rows):

SELECT connect_by_root(dept.id) AS dept_id, 
       CONNECT_BY_ROOT(dept.parent_id) AS parent_id, 
       emp.id AS id_e
FROM emp
RIGHT JOIN dept ON emp.dept = dept.id
CONNECT BY PRIOR dept.id = dept.parent_id

EDIT: Here is a fiddle of the scenario

hierarchical oracle sql
1 Answers

I came up with a solution, using a recursive CTE to parse the hierarchy and retrieve each possible way a department can connect to the root, which is then joined with the employee table.

Could you give it a try, and let me know if it works?

WITH RCTE_DEPT(id,root,parent_id) AS(
  SELECT id,parent_id, id
  FROM dept
  UNION ALL
  SELECT dept.id,root,RCTE_DEPT.parent_id
  FROM dept
  JOIN RCTE_DEPT ON
    dept.parent_ID = RCTE_DEPT.id)
SELECT emp.id as ID_E, RCTE_DEPT.root as ROOT, RCTE_DEPT.parent_id as DEPT
FROM emp
JOIN RCTE_DEPT ON emp.DEPT = RCTE_DEPT.id 

ORDER BY ID_E ASC, ROOT ASC, DEPT ASC

Here is a fiddle.

user_118189940
Related