Hierarchical queries using CONNECT BY logic are discussed quite frequently. Often in terms of EMP, the famous demo table that has a self referencing foreign key – MGR to EMPNO – that allows a tree-style resultset, representing the organization hierarchy. Hierarchical queries can be created across multiple tables, as I have discussed in the post on Bill of Materials, where I used a self-referencing intersection table as basis for a hierarchical query, see Bill of Materials and Hierarchical Queries – Which Component contains a component that….
When it comes to presenting a tree-style user interface widget, it is not uncommon to see nodes representing records in different tables. For example the Tree in the Repository Object Navigator in Oracle Designer contains Entities, Attributes, Table Definitions, Columns etc., alle records in different tables (at least as of Designer 6i). The Tree in Forms Builder contains Blocks, Triggers, Items etc. A tree in a typical HRM application might contain Department nodes as well as Employee nodes. See for example:
One way of constructing such a tree in a single query, is the following:
with emps as
(
select deptno empno -- the department nodes; the referenced key simulates the EMP key
, dname ename
, -1 mgr -- the reference to parent nodes; not applicable to DEPT nodes
from dept
union
select empno -- the highest-level employee nodes, the link between DEPT and EMP nodes
, ename
, deptno mgr -- this interface layer, bridge between EMP and DEPT nodes, uses its DEPT references as a simulated MGR Parent-node reference
from emp
where job ='MANAGER'
or mgr is null
union
select empno -- all EMP-nodes
, ename
, mgr
from emp
)
select lpad(' ', level*3)||ename ename
from emps
connect
by prior empno = mgr
start
with mgr = -1
/
The results are:
ACCOUNTING
CLARK
MILLER
KING
JONES
SCOTT
ADAMS
FORD
SMITH
TURNER
MARTIN
BLAKE
ALLEN
WARD
JAMES
CLARK
MILLER
RESEARCH
SALES
BLAKE
ALLEN
WARD
JAMES
OPERATIONS
24 rows selected.
Now we would like to find all Employees whose name contain an A (where ename like ‘%A’). And we would like to present those records in a tree-like structure. The query for this looks like:
with emps as
(
select deptno empno -- the department nodes; the referenced key simulates the EMP key
, dname ename
, -1 mgr -- the reference to parent nodes; not applicable to DEPT nodes
, null marker
from dept
union
select empno -- the highest-level employee nodes, the link between DEPT and EMP nodes
, ename
, deptno mgr -- this interface layer, bridge between EMP and DEPT nodes, uses its DEPT refe
, case
when ename like '%A%'
then 'X'
end marker
from emp
where job ='MANAGER'
or mgr is null
union
select empno -- all EMP-nodes
, ename
, mgr
, case
when ename like '%A%'
then 'X'
end marker
from emp
)
, tree_nodes as
(
select distinct
ename
, empno
, mgr
from emps
connect
by PRIOR mgr=empno -- note that the connect by condition is exactly the reverse of the one
start
with marker is not null
)
select lpad(' ' , level * 4)||ename
from tree_nodes
connect by prior empno = mgr
start with mgr = -1
/
following the approach discussed in Selecting a ‘pruned tree’ with selected nodes and all their ancestors – Hierarchical SQL Queries and Bottom-Up Trees . The query result looks like:
LPAD('',LEVEL*4)||ENAME
--------------------------------------------------------------------------------
ACCOUNTING
CLARK
KING
BLAKE
ALLEN
JAMES
WARD
CLARK
JONES
FORD
SMITH
TURNER
MARTIN
SCOTT
ADAMS
SALES
BLAKE
ALLEN
JAMES
WARD
Resources
Querying Hierarchies: Top-of-the-Line Support – part 2 by Jonathan Gennick
