Using An Outer Join in a Query

From NeoWiki

Jump to: navigation, search
This article is part of the To Base and Back Again tutorial series.


By default, Base uses Inner Joins when you create a query that combines fields from two or more tables. (Actually, Base usually uses WHERE statements rather than the INNER JOIN command in such circumstances, but Base labels the join type as an inner join, and the result is generally the same). An INNER JOIN selects only those records from each table that have corresponding records in the other tables used. When we created the All Sightings query, for example, the query returned only the AOU_ID fields that matched birds our fictional user has actually identified, not all the AOU_ID field values.

In some cases, however, you want to view all the records from one table, whether or not there are corresponding records in the other table. For example, not all of photos stored in the Bird_Pictures table correspond to a record in the Field_Trips table. But suppose we want to see a list of all the photos we've taken, but we also want to know where the photos were taken, if they were taken on a field trip. We can do this by using an OUTER JOIN.

Contents

Using an Outer Join to Connect Two Tables

  1. Click on the Queries icon in the left hand column of the main database document window.
  2. Click on the Create Table in Design View line in the Tasks name. The Query Design window opens, as will the Add Table or Query window.
  3. In the Add Table or Query window, add the Bird_Pictures and Field_Trips tables by clicking each one in turn and clicking Add.
  4. Close the Add Table or Query window.
  5. Add the following fields from the tables to the columns in the bottom section of the Query Design window: (See this tutorial if you aren't sure how to do so.)
    BirdPictured from the Bird_Pictures table
    DateTaken from the Bird_Pictures table
    Location from Field_Trips table.
  6. In the section of the Query Design window where the tables are shown, you should notice a line connecting PicFieldTripID in the Bird_Pictures table and FieldTrip_ID in the Field_Trips table. If there are no such lines connecting the tables, drag one of these two fields to the other field to create the line.
  7. Click on the connecting line so it turns blue.
  8. Double click on the selected line. The Join Properties window opens.
  9. In the Options section of the window, there is a Type drop-down that is currently set for Inner. Click on the drop-down to see that your choices also include Left join and Right join. A Left join pulls all the records from the field listed in the left column of the Fields Involved section, and only those in the right column that match those in the left. A right join does the reverse. Note that a detailed explanation, naming the tables and fields involved, appears in the Options section as you change join types.
  10. Assuming that the PicFieldTrip_ID field is in the left-hand column, select Left join as the join type.
  11. Click OK
  12. To test the results of our query, click on the Run Query icon on the toolbar. (The data sources icon with the green arrow).
  13. Save the Query by clicking on the Save icon on the tool bar or pressing Command-S. You will be prompted to name the Query. I called mine Bird_Pictures_Locations.


Using the Query-in-a-Query Approach to Combine More than Two Tables

The above process works when only two tables are involved. Using a left or right join in a query combining more than two tables often provides undesired results, due to how Base turns the design view settings into SQL statements. Individuals proficient in SQL can create correctly ordered SELECT statements using LEFT or RIGHT JOINS in SQL view. For those of us less comfortable with SQL, it is also possible to achieve the desired results by using the Query-in-a-Query approach. (Sometimes called QiQ for short)

Essentially, you use two "side" queries to combine the various tables. These two "side" queries are then combined in the "central" query which uses a left or right join. (Note that the terms "side query" and "central query" are unique to this author).


Determine the Sides of the Central Query

The first step of this process is to determine which tables need to be combined in which side query. Think this through carefully. Start with the goal of the central query, and work your way back to figure out how the side queries should be configured.

For our example, we want to produce a Query that lists all Birds pictured, ordered by AOU_ID number and the date the picture was taken. If the picture was taken during a field trip, we also want the FieldTrip_ID, the location it was taken, and the state that location is found in. We want to list all the birds pictured, sorted by AOU_ID order. We want to list only those field trips (and corresponding location information) that correspond to the records in the Birds_Pictures table. So our two sides will be:

Bird Side: AOU_ID, BirdPictured, PicFieldTrip_ID, DateTaken
Field Trips Side: FieldTrip_ID, Location, StateOrProvince

Note: Each side query must include a field that can relate to a field in the other side query. Our original description of our needed results above did not mention the PicFieldTrip_ID field. But we need to include it in the Bird side query in order to relate that query to the Field Trips side query.

Create the Side Queries

Note: Because creating Queries has been covered in detail earlier in this tutorial series, the instructions below presume that the reader is familiar with the basics of creating queries. Those who are not should first read Using a Query to Combine Fields From Different Tables

  1. Create a query named BirdPix_AOU_ID. Include the AOU_ID, BirdPictured, PicFieldTrip_ID and DateTaken fields from the Bird_Pictures and AOU_Birdlist tables.
  2. Create a query name FieldTrips_Locations. Include the FieldTrip_ID, Location, and StateOrProvince fields from the Field_Trips and Locations tables.

Create the Central Query

  1. Create a new query named Pix_Locations
  2. Add the BirdPix_AOU_ID and FieldTrips_Locations queries
  3. Add the following fields to the columns in the bottom of the Query Design window:
    AOU_ID
    BirdPictured
    DateTaken
    Location
    StateOrProvince
  4. Drag the PicFieldTrip_ID field from the BirdPix_AOU_ID mini-window to the FieldTrip_ID field in the FieldTrips_Locations mini-window. A line appears between the two fields.
  5. Double click on this line to open the Join properties window.
  6. In the Options section, set the correct type of join in the Type drop-down. Read the description for a left and right join very carefully; left and right do not necessarily refer to the relative positions of the tables in the Query Design window. You want all the records from the BirdPix_AOU_ID "table." When I created this query, that meant a right join.

Notes

  • You can add filtering criteria to either side join, or to the central join. See Filtering in Queries and Tables.
  • You can create very complex queries by using right or left joins in side queries. Just keep in mind that any given query using a right or left join must only combine two tables or queries. If need be, you can create a chain of side and central queries feeding into one "master" query.


This article in other languages: Français
Retrieved from "https://neowiki.neooffice.org/index.php/Using_An_Outer_Join_in_a_Query"
Personal tools