A self join joins a table to itself. This is useful for
hierarchical relationships such as parts lists, employee to
supervisor reporting, customer referrals, etc. These are all
recursive relationships.
The
purpose of this article is show how to create a Microsoft
Access query based on these recursive relationships. We will consider
the one-to-many case, the most common, in this article.
You can download the Microsoft Access sample database,
SelfJoin.mdb, at
box.net. A downloadable PowerPoint presentation version
of this article can be found on
Slideshare.
Business Scenario
We want to construct a query that will list all employees and
their supervisor, if they have one. The employees and their
supervisors are in a single employees table. After all, the
supervisors are also employees.
Database Design
Relationship Designer
Below is a screenshot of the Microsoft (MS) Access relationship
design of our sample SelfJoin database. It is the same as
the Factory 2000 sample database design. Note the MgrNo
foreign key. It actually references the EmpNo in the same
table.
Employees Table
Below is a screenshot of the sample data in
the employees table. The MgrNo field references the EmpNo
field in the same table.
Betty reports to Alicia who in turn reports to Mary. In this
example Mary does not have a direct supervisor. This example
is similar to many recursive relationships in which the top
of the tree is denoted by a null foreign key.
Designing the self join query
Step 1
Our self join query will be a normal query,
joining two tables. The difference is that this time the two
tables are really the same table. Just add the employees
table twice to the query designer. The second time the table
is added a _1 suffix is appended. So far as MS Access is
concerned we will be joining two different tables named
employees and employees_1.
Assign an Alias
The next step is to assign an alias to the second employees
table above. Right click on the table name in the MS Access
query designer and assign an alias as shown below. Call it
managers. This step is not strictly necessary but your query
will be more meaningful if you do it. What this does is give
the second table what is called a table alias which is
another name for the table. All references to the table must
then use this alias. Table aliases are often used in SQL to
give a table a short name that a query can use. They are not
so common in MS Access but are, nevertheless, helpful here.
Create Table Relationships
Create the relationship between the two tables. In this case
we want to make the relationship an outer join. This means
that all employees will be listed even if they have no
supervisor. If the MgrNo foreign key has a null value then a
normal inner join would exclude that employee.
Finish by adding the fields you want to show to the query
designer QBE grid. Note how the table names in the QBE grid
reflect the new alias we created. I added a calculated
field, Relationship, which will contain the value "Reports
to". This is just an added touch to enhance the display. It
is not necessary for the self join.
Save the query and run it.
Query results
Below are the results of running the query with the sample
data. All three employees are listed along with their
manager.
Conclusion
This article showed how to
create a self join in Microsoft Access using a table alias.
Hopefully, you will find these self joins a useful tool in
creating Microsoft Access database applications. I am sure
you will find many uses for them.
Dan D'Urso
Laguna Niguel
Orange County, CA 92677
November 2013
articles at dhdurso dot org