One final topic: join properties. Once again, let us modify
our business rules a bit. We want a join of employees and
labor to default to an outer join. That way we will see all
employees even if they have not recorded time during the
period of our analysis.
To do this we will modify the join properties of the
relationship between employees and the labor table.
Right click on the relationship line between employees and
labor.
You can also select the relationships line and then go to the
ribbon.
In either case click edit relationships to bring up the edit
relationship dialogue.
Click on join type. This will bring up the join properties
dialogue box.
The three choices above represent three join types.
Join Types
Inner Join - This means that
the primary key (PK) in the employee records is matched against the
foreign key (FK) in the labor table. If they match that record is
retrieved. If they don't match it is not. In other words
an employee with no labor tickets will not show in the
result yet. This is the default join type and is usually
what you want. But there are many cases in which it is
not. See below
Left Outer Join - This is the one we want
for this example. It is an
outer join. In particular a left outer join. The "left"
table is the one with the PK. In this case it means
that all employee records will be retrieved whether or
not they have a matching
labor ticket. If there is no match, the missing fields
in the result will be filled with nulls. This join type
is very common for reporting. As an example: "let me see
a list of all my employees over the report period and
the hours they worked. If they did not work than show a
blank; don't drop them from the report."
Right outer join - Similar to
the above but adds all unmatched records from the right
table instead of the left table. In this it would be all
labor tickets with no corresponding employee. There
would not be any of these if you are enforcing
referential integrity as we are.
Relationship Window
Click OK and you will see the relationship design
window below. Note the little arrow at the many end of the
relationship line between employees and labor. This denotes
an outer join. It point to the table that will have added
phantom records in the result set. In this case when
employees and labor are joined the employees with no
matching labor records will still show in the result set but
the labor fields will be set to nulls.
As a reminder, I would NOT normally set up the relationship
design this way. This is just for illustration. You would
probably want to undo the outer join and set the
relationship between labor and work orders to enforce
referential integrity. But you know how to do that now.
Hopefully, you have found this article useful. Many of our
classes start off with this little database and we like to
have the students walk through the relationship design.
Dan D'Urso
Laguna Niguel, CA 92677
August 2012
articles at dhdurso dot org
