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
