Hierarchical query with nodes from different tables – DEPT and EMP nodes in one tree

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