Recipe13.4.Finding All Child Rows for a Given Parent Row

Recipe 13.4. Finding All Child Rows for a Given Parent Row

Problem

You want to find all the employees who work for JONES, either directly or indirectly (i.e., they work for someone who works for JONES). The list of employees under JONES is shown below (JONES is included in the result set):

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

Solution

Being able to move to the absolute top or bottom of a tree is extremely useful. For this solution there is no special formatting necessary. The goal is to simply return all employees who work under employee JONES, including JONES himself. This type of query really shows the usefulness of recursive SQL extensions like Oracle's CONNECT BY and SQL Server's/DB2's WITH clause.

DB2 and SQL Server

Use the recursive WITH clause to find all employees under JONES. Begin with JONES by specifying WHERE ENAME = 'JONES' in the first of the two union queries:

  1   with x (ename,empno)  2     as (  3 select ename,empno  4   from emp  5  where ename = 'JONES'  6  union all  7 select e.ename, e.empno  8   from emp e, x  9  where x.empno = e.mgr 10 ) 11 select ename 12   from x 

Oracle

Use the CONNECT BY clause and specify START WITH ENAME = 'JONES' to find all the employees under JONES:

 1 select ename 2   from emp 3  start with ename = 'JONES' 4 connect by prior empno = mgr 

PostgreSQL and MySQL

You must know in advance how many nodes there are in the tree. The following queries show how to determine the depth of the hierarchy:

  /* find JONES' EMPNO */ select ename,empno,mgr   from emp  where ename = 'JONES' ENAME            EMPNO       MGR ---------- ----------- --------- JONES             7566      7839  /* are there any employees who work directly under JONES? */ select count(*)   from emp  where mgr = 7566   COUNT(*)  ---------          2  /* there are two employees under JONES, find their EMPNOs */ select ename,empno,mgr   from emp  where mgr = 7566 ENAME            EMPNO         MGR ---------- ----------- ----------- SCOTT             7788        7566 FORD              7902        7566  /* are there any employees under SCOTT or FORD? */ select count(*)   from emp  where mgr in (7788,7902)   COUNT(*)  ---------          2  /* there are two employees under SCOTT or FORD, find their EMPNOs */ select ename,empno,mgr   from emp  where mgr in (7788,7902) ENAME           EMPNO      MGR --------- ----------- -------- SMITH            7369     7902 ADAMS            7876     7788  /* are there any employees under SMITH or ADAMS? */ select count(*)   from emp  where mgr in (7369,7876)   COUNT(*) ----------               0 

The hierarchy starting from JONES ends with employees SMITH and ADAMS. That makes the hierarchy three levels deep. Now that you know the depth, you can begin to traverse the hierarchy from top to bottom.

First, self join table EMP twice. Then unpivot inline view X to transform three columns with two rows into one column with six rows (in PostgreSQL, you can use GENERATE_SERIES(1,6) as an alternative to querying the T100 pivot table):

  1  select distinct  2         case t100.id  3              when 1 then root  4              when 2 then branch  5              else        leaf  6         end as JONES_SUBORDINATES  7   from (  8 select a.ename as root,  9        b.ename as branch, 10        c.ename as leaf 11   from emp a, emp b, emp c 12  where a.ename = 'JONES' 13    and a.empno = b.mgr 14    and b.empno = c.mgr 15        ) x, 16        t100 17  where t100.id <= 6 

As an alternative, you can use views and UNION the results. If you create the following views:

 create view v1 as select ename,mgr,empno   from emp  where ename = 'JONES'  create view v2  as  select ename,mgr,empno    from emp   where mgr = (select empno from v1)  create view v3  as  select ename,mgr,empno    from emp   where mgr in (select empno from v2) 

the solution then becomes:

 select ename from v1  union select ename from v2  union select ename from v3 

Discussion

DB2 and SQL Server

The recursive WITH clause makes this a relatively easy problem to solve. The first part of the WITH clause, the upper part of the UNION ALL, returns the row for employee JONES. You need to return ENAME to see the name and EMPNO so you can use it to join on. The lower part of the UNION ALL recursively joins EMP.MGR to X.EMPNO. The join condition will be applied until the result set is exhausted.

Oracle

The START WTH clause tells the query to make JONES the root node. The condition in the CONNECT BY clause drives the tree walk and will run until the condition is no longer true.

PostgreSQL and MySQL

The technique used here is the same as that of the second recipe in this chapter, "Expressing a Child-Parent-Grandparent Relationship." A major drawback is that you must know in advance the depth of the hierarchy.