Share this Page URL

Chapter 9. Extending Your Use of Queries > Using Outer Joins and Self-Joins - Pg. 329

Extending Your Use of Queries 329 4. 5. The Join Properties dialog box shows which table is on the left and which is on the right and identifies the field (referred to as a column in the dialog box) that joins the tables. The dialog box provides three options for the type of join. Option 1 is an inner join. Options 2 and 3 are for outer joins--Option 2 a left outer join, and Option 3 a right outer join. Select Option 2, and then click OK. On the toolbar, click the Run button. In the query's results, you'll see that with an outer join, the query returns 50 records. Four campaigns haven't incurred any expenses yet. 6. 7. To determine which of the campaigns have no expenses recorded, you can scroll through the set of records. In this example, the number of records is manageable, but imagine scrolling through hundreds of records to find two customers without an order ID. You could sort records so that records with Null values in the field you sort by appear at the top, but another way to find records in the one table without matching records in the many table is to use the outer join and the criteria expression Is Null in a required field from the table on the many side. Switch back to Query Design view, and then enter Is Null in the Criteria row for ExpenseType. Run the query again, and you'll see the four countries without a matching order, as shown here: