SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-57
Types of Join Queries
Using a Left Join to Show Hierarchical Relationships
You can use the left join operation to display hierarchical relationships among data.
These examples use the sample database shown in Example 1-10.
This query requests information about employees and their dependents from three
tables: DEPT, EMPLOYEE, and DEPD:
SELECT DT.DEPTNAME, E.EMP_LNAME, E.EMP_FNAME, DD.DEP_FNAME
FROM DEPT DT, EMPLOYEE E, DEPD DD
WHERE DT.DEPTNO = E.DEPTNO AND E.EMPNO = DD.EMPNO ;
Because this query specifies an inner join operation, the result does not include
departments that have no employees or employees who have no dependents:
DEPTNAME EMP_LNAME EMP_FNAME DEP_FNAME
------------ --------------- ------------- ----------
Maintenance Gray Tina William
Maintenance Gray Tina Gwendolyn
--- 2 row(s) selected.
Example 1-10. Sample Tables For Hierarchical Relationship Examples
DEPT Table
DEPTNO DEPTNAME MANAGER RPTDEPT LOCATION
------ ------------- ------- ------- ------------
101 Accounting 23 9000 CHICAGO
102 Maintenance 213 1000 CHICAGO
103 Personnel 32 9000 LOS ANGELES
EMPLOYEE Table
EMPNO EMP_FNAME EMP_LNAME DEPTNO JOBCODE SALARY
----- ------------- -------------- ------ ------- -----------
10 John Smith 101 100 175500.00
20 Tina Gray 102 100 137000.10
DEPD Table
DEPDNO DEP_FNAME DEP_LNAME EMPNO
------ ------------- ------------------ -----
521 William Gray 20
522 Gwendolyn Gray 20
Note. The examples in this subsection associate correlation names—DT, E, and DD—with the
table names DEPT, EMPLOYEE, and DEPD.