Recipe13.1.Expressing a Parent-Child Relationship

Recipe 13.1. Expressing a Parent-Child Relationship

Problem

You want to include parent information along with data from child records. For example, you want to display each employee's name along with the name of his manager. You want to return the following result set:

 EMPS_AND_MGRS ------------------------------ FORD works for JONES SCOTT works for JONES JAMES works for BLAKE TURNER works for BLAKE MARTIN works for BLAKE WARD works for BLAKE ALLEN works for BLAKE MILLER works for CLARK ADAMS works for SCOTT CLARK works for KING BLAKE works for KING JONES works for KING SMITH works for FORD 

Solution

Self join EMP on MGR and EMPNO to find the name of each employee's manager. Then use your RDBMS's supplied function(s) for string concatenation to generate the strings in the desired result set.

DB2, Oracle, and PostgreSQL

Self join on EMP. Then use the double vertical-bar (||) concatenation operator:

 1 select a.ename || ' works for ' || b.ename as emps_and_mgrs 2   from emp a, emp b 3  where a.mgr = b.empno 

MySQL

Self join on EMP. Then use the concatenation function CONCAT:

 1 select concat(a.ename, ' works for ',b.ename) as emps_and_mgrs 2   from emp a, emp b 3  where a.mgr = b.empno 

SQL Server

Self join on EMP. Then use the plus sign (+) as the concatenation operator:

 1 select a.ename + ' works for ' + b.ename as emps_and_mgrs 2   from emp a, emp b 3  where a.mgr = b.empno 

Discussion

The implementation is essentially the same for all the solutions. The difference lies only in the method of string concatenation, and thus one discussion will cover all of the solutions.

The key is the join between MGR and EMPNO. The fist step is to build a Cartesian product by joining EMP to itself (only a portion of the rows returned by the Cartesian product is shown below):

  select a.empno, b.empno   from emp a, emp b EMPNO        MGR ----- ----------  7369       7369  7369       7499  7369       7521  7369       7566  7369       7654  7369       7698  7369       7782  7369       7788  7369       7839  7369       7844  7369       7876  7369       7900  7369       7902  7369       7934  7499       7369  7499       7499  7499       7521  7499       7566  7499       7654  7499       7698  7499       7782  7499       7788  7499       7839  7499       7844  7499       7876  7499       7900  7499       7902  7499       7934 

As you can see, by using a Cartesian product you are returning every possible EMPNO/EMPNO combination (such that it looks like the manager for EMPNO 7369 is all the other employees in the table, including EMPNO 7369).

The next step is to filter the results such that you return only each employee and his manager's EMPNO. Accomplish this by joining on MGR and EMPNO:

  1 select a.empno, b.empno mgr 2   from emp a, emp b 3  where a.mgr = b.empno      EMPNO        MGR ---------- ----------       7902       7566       7788       7566       7900       7698       7844       7698       7654       7698       7521       7698       7499       7698       7934       7782       7876       7788       7782       7839       7698       7839       7566       7839       7369       7902 

Now that you have each employee and the EMPNO of his manager, you can return the name of each manager by simply selecting B.ENAME rather than B.EMPNO. If after some practice you have difficulty grasping how this works, you can use a scalar subquery rather than a self join to get the answer:

  select a.ename,        (select b.ename           from emp b          where b.empno = a.mgr) as mgr   from emp a ENAME      MGR ---------- ---------- SMITH      FORD ALLEN      BLAKE WARD       BLAKE JONES      KING MARTIN     BLAKE BLAKE      KING CLARK      KING SCOTT      JONES KING TURNER     BLAKE ADAMS      SCOTT JAMES      BLAKE FORD       JONES MILLER     CLARK 

The scalar subquery version is equivalent to the self join, except for one row: employee KING is in the result set, but that is not the case with the self join. "Why not?" you might ask. Remember, NULL is never equal to anything, not even itself. In the self-join solution, you use an equi-join between EMPNO and MGR, thus filtering out any employees who have NULL for MGR. To see employee KING when using the self-join method, you must outer join as shown in the following two queries. The first solution uses the ANSI outer join while the second uses the Oracle outer-join syntax. The output is the same for both and is shown following the second query:

  /* ANSI */ select a.ename, b.ename mgr   from emp a left join emp b     on (a.mgr = b.empno) /* Oracle */ select a.ename, b.ename mgr   from emp a, emp b  where a.mgr = b.empno (+) ENAME      MGR ---------- ---------- FORD       JONES SCOTT      JONES JAMES      BLAKE TURNER     BLAKE MARTIN     BLAKE WARD       BLAKE ALLEN      BLAKE MILLER     CLARK ADAMS      SCOTT CLARK      KING BLAKE      KING JONES      KING SMITH      FORD KING