Self
(Recursive) Joins Explained
Logically, this
is very similar to SQL.
Check out the “View” --> “SQL View” after completing the design in
the GUI editor.
A "Self-
Join" is used to relate one field in a table to another field in the same
table.
It’s called a
self-join because one field in the table is
“joined” to another field in its “self.”
Why would you do
this ?
For example, let’s look at
employees and their managers :
Employees can be described by attributes such as
their name, address, birth and hire dates and other relevant data.
Employees can be uniquely identified by an “Employee
ID” number as well.
Managers in the
same company are also employees
and are very similar in their data structure :
The same
attributes can be used to describe managers
as are used to describe employees,
because managers ARE employees.
We should store managers and employees in separate
tables,
if we wanted to adhere strictly to “normalization” rules that state
that a table should only contain attributes describing a single type of object.
Storing them both
in the same table is a defensible violation of normalization rules.
Doing so can produce a more meaningful and efficient relationship,
but it takes some extra planning.
Since both
employees and managers are considered employees,
everyone needs to be assigned “Employee ID”
numbers.
In addition, non-management employees need to be assigned
an attribute that identifies their manager.
Since each
employee’s manager is also an employee,
each employee’s record should contain a field that
identifies the manager’s employee ID number.
Defining the Relationship
To define a
relationship in Access, it is usually necessary to
drag one field in one table to the related field in the other table.
Fine, but how do
you do this when both fields are in the same table ?
To do this
requires creating two copies of the same table in the query design grid :
To make two copies of the same table is easy.
In fact, it’s so easy you’ve probably done this by accident,
and had to delete the duplicate to make a query work properly.
After one window is
displayed for the table, just add the same table again :
In SQL, this is
accomplished by using an “alias” for the second copy of the table.
See below
Notice that the second copy of the table has a slightly different titles.
It has the same name; actually, it just has “_1”
added to it,
meaning that it’s a copy of the “Employees”
table.
(In fact, if you
make a third copy, it would be “Employees_2”
which fortunately, we won’t have to do.
Two copies is enough to keep track of your boss, his boss,
and even his boss’s boss.)
So, now you have two copies of
the “Employees” table :
Assigning an Alias to a
“Cloned” Table
Whenever two
copies of the same table are displayed in the design grid,
Access “aliases” the copy with the suffix (“_1”), as shown above.
This means that the “Employees_1” table is a copy of the “Employees” table.
Let’s give them
our own names so we can work with them better :
If Access can “alias” the table by giving it a different
name,
you can “alias” the copy with a more
meaningful name.
Here’s how :
Right-click on the
title bar of the “copy” (“Employees_1”)
to display the table’s properties dialog box :
and change the alias to something better, like “Managers” :
(Managers are employees, too, remember.)
so it looks like
this :
Now the two copies (of the same table) even look like
different tables,
except that they are, in fact, “clones” of the same table
and each has a name describing how it will be used.
Now, since they at least appear to be two different
tables,
you can drag the ”MgrID” field across to
the “other” table’s “EmpID” field
to accomplish the desired relationship :
SELECT *
FROM [Employees Table]
INNER
JOIN [Employees Table] AS [Managers Table]
ON [Employees Table].ManagerID
= [Managers Table].EmployeeID;
Note that the employee’s ManagerID is the manager’s EmployeeID,
and they
are both in the same table !
Now it can more
clearly be seen that the “Employees”
table
contains a field that relates each employee to a row in the “Managers” table,
when the “Managers” table is, in fact,
the “Employees” table.
A manager can now
be assigned an employee who works for him or her
by storing the manager’s “EmpID” in the
employee’s “MgrID” field :
Note :
The
“Big Boss” “MgrID” = 0, because he has no manager :
He’s the boss and has no boss above him (or her.)
Running the query using this relationship yields the following results :
So, Barb and Fred work for Mary.
and Mary works for “Big Boss.”
Note that the“MgrID” in Fred’s record is
Mary’s “EmpID” number,
which are the foreign and primary keys, respectively.
Similarly, Mary’s “MgrID” is the “EmpID” number of the “Big Boss.”
If someone was hired to work for Fred,
the new employee’s might have “EmpID” number 5,
but would have 4 as their “MgrID” because Fred is employee # 4.
(This arrangement actually allows for many levels of management
as shown in these examples, but is still a “self-join.”)
Note that the “Big
Boss” doesn’t work for anyone because
he has no matching “MgrID” key (0) in any
other row
(see “Employees” table in figure above)