Using An Outer Join in a Query

From NeoWiki

Revision as of 02:20, 27 October 2007 by Lorinda (Talk | contribs)
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.

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)

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

Personal tools