6. MICROSOFT ACCESS QUERIES – ADVANCED
6.8.4: Self-Join
A self-join, also called a recursive join, is a case where a table is joined to itself.
Consider the Company database and suppose we must obtain a list of employees who report to another employee named Raphael Delaney (i.e., List the employees Raphael Delaney supervises). To do this we need to find the row in Employee for Raphael Delaney and then join that row to other rows of Employee where the supervisor field is equal to the empId field for Raphael. When we build the query in MS Access we simply add the Employee table to the relationships area twice. One copy of Employee will be named Employee_1. Consider the following query:
Note the following:
-
-
- the criteria specifies the row in Employee will be that of Raphael Delaney
- the join line connects supervisor to empId and so rows of Employee_1 will be employees who report to Raphael.
-
Exercises
1) Consider the Genealogy database and develop queries to obtain:
- the father of Peter Chan.
- the mother of Peter Chan.
- the father and mother of Peter Chan.
2) Consider the Orders database and the Employee table:
- Write a query to list the employee who does not report to anyone.
- Write a query to list each employee and the number of employees they supervise.
