Hierarchical query on employees and their supervisor in Oracle HR
We all know the famous emp/dept hierarchical queries to show employees belonging to a department and employees with their manager. I recently came across a question on how to query all employees and their managers (called supervisors in HR) in a hierarchical manor so I created this query as combination of what I've read in https://technology.amis.nl/2005/08/16/hierarchical-query-with-nodes-from-different-tables-dept-and-emp-nodes-in-one-tree/) and Oracle database schema.Note that the employee - supervisor link can be circular. In that case you will not find those employees in the tree, since there is no top node where the manager (supervisor_id) is empty.
with emps as
(
-- The managers
select s.person_id empno
, s.first_name || ' ' || s.last_name ename
, sa.supervisor_id mgr
from per_people_x s
, per_assignments_x sa
where s.person_id = sa.person_id
and exists (select 1 from per_assignments_x a where a.supervisor_id = s.person_id)
union
-- The employees
select p.person_id empno -- all EMP-nodes
, p.first_name || ' ' || p.last_name ename
, a.supervisor_id mgr
from per_people_x p, per_assignments_x a
where p.person_id = a.person_id
)
select lpad(' ', level*3)||ename ename
from emps
connect
by prior empno = mgr
start
with
nvl (mgr,-1) = -1
/
