Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Use of Outer Joins in Voyager Prepackaged Access Reports

    • Product: Voyager
    • Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare

     

    Question

    Use of Outer Joins in Voyager Prepackaged Access Reports

    Answer

    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:

    clipboard_e977a7cd407ecc309c36d198a81d7ebe0.png

     

     

    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:

     

    clipboard_e898943b4a9f6e61844321a360ee98968.png

     

    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:

    clipboard_e62a410cded852df413689a009cce3cfd.png

     

    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:

    1. In the Query Design View, draw a line between fields in two tables
    2. Right-click on the join to open "Join Properties"
    3. In most Outer Join cases you will select the second option (left join)

    clipboard_e7e340b65809b14edf74ff49f938e0da9.png

     

    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
    • Was this article helpful?