13. Hierarchical Queries

Expressing a Parent-Child Relationship

We want to include parent information along with data from child records. For example, we want to display each employee’s name along with the name of their manager.

Sample Output

-- Self join the table
select a.ename || ' works for ' || b.ename as emps_and_mgrs
from emp a, emp b
where a.mgr = b.empno

Expressing a Child-Parent-Grandparent Relationship

We want to find Manager of a Manager of an employee. For example, Employee CLARK works for KING, and CLARK was in turn a manager for another employee SHARK. (Three-Tier Relationship)

SHARK -> CLARK -> KING

select ltrim(
    sys_connect_by_path(ename,'-->'),
    '-->') leaf___branch___root
from emp
where level = 3
start with ename = 'MILLER'
connect by prior mgr = empno

Creating a Hierarchical View of a Table

In the case of the EMP table, employee KING has no manager, so KING is the root node. You want to display, starting from KING, all employees under KING and all employees (if any) under KING’s subordinates.

Sample Output

select ltrim(
    sys_connect_by_path(ename,' - '),
    ' - ') emp_tree
from emp
start with mgr is null
connect by prior empno=mgr
order by 1

Finding All Child Rows for a Given Parent Row

We want to find all the employees who work for JONES, either directly or indirectly.

Sample Output

ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH

-- Oracle
select ename
from emp
start with ename = 'JONES'
connect by prior empno = mgr

Determining Which Rows Are Leaf, Branch, or Root Nodes

We want to determine what type of node a given row is: a leaf, branch, or root.

Sample Output

select 
    ename,
    connect_by_isleaf is_leaf,
        (select count(*) from emp e
        where e.mgr = emp.empno
        and emp.mgr is not null
        and rownum = 1) is_branch,
    decode(ename,connect_by_root(ename),1,0) is_root
from emp
start with mgr is null
connect by prior empno = mgr
order by 4 desc, 3 desc