- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Use of Outer Joins in Voyager Prepackaged Access Reports
In Access, "Joins" combine rows from two or more tables by linking on a common field.
There are "Inner Joins" and "Outer Joins".
Inner Joins are also called "Simple Joins" and return only those records that have a match in both tables:
An Outer Join returns records from the left, right, or both tables (depending on the type of join), even if there is no matching record in the other table:
You use an Outer Join to avoid dropping records when two tables do not have matching values.
The following example is a Left Outer Join between the ITEM and the ITEM_BARCODE tables. It will not exclude unbarcoded items from the result set:
If you do not use an Outer Join in these cases:
- Unbarcoded items will not be returned by a query that uses the ITEM_BARCODE table.
- E-resource MFHDs without items will not be returned in a query that uses the ITEM table.
- BIBs without subject heading subdivisions will not be returned in a query that uses the BIB_SUBDIVISION table.
To create a join in MS Access:
- In the Query Design View, draw a line between fields in two tables
- Right-click on the join to open "Join Properties"
- In most Outer Join cases you will select the second option (left join)
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 08-Jun-2021