https://neowiki.neooffice.org/index.php?title=Using_An_Outer_Join_in_a_Query&action=history&feed=atomUsing An Outer Join in a Query - Revision history2024-03-29T11:39:40ZRevision history for this page on the wikiMediaWiki 1.10.1https://neowiki.neooffice.org/index.php?title=Using_An_Outer_Join_in_a_Query&diff=12486&oldid=prevJgd: botlangbar to Fr page2007-10-28T09:16:42Z<p>botlangbar to Fr page</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 09:16, 28 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 63:</strong></td>
<td colspan="2" align="left"><strong>Line 63:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*You can add filtering criteria to either side join, or to the central join. See [[Filtering in Queries and Tables]].</td><td> </td><td style="background: #eee; font-size: smaller;">*You can add filtering criteria to either side join, or to the central join. See [[Filtering in Queries and Tables]].</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*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.</td><td> </td><td style="background: #eee; font-size: smaller;">*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.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">{{botlangbarEN|[[Fr:Utiliser une Jointure Externe dans une Requête|Français]]}}</td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=Using_An_Outer_Join_in_a_Query&diff=12468&oldid=prevLorinda: fix some typos2007-10-27T03:19:39Z<p>fix some typos</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 03:19, 27 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 2:</strong></td>
<td colspan="2" align="left"><strong>Line 2:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">By default, Base uses Inner Joins when you [[Using a Query to Combine Fields From Different Tables|create a query that combines fields from two or more tables<del style="color: red; font-weight: bold; text-decoration: none;">.</del>]]. (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 [[Using a Query to Combine Fields From Different Tables|All Sightings]] query, for example, the query returned only the '''<del style="color: red; font-weight: bold; text-decoration: none;">AOU-ID</del>''' fields that matched birds our fictional user has actually identified, not all the '''AOU_ID''' field values.</td><td>+</td><td style="background: #cfc; font-size: smaller;">By default, Base uses Inner Joins when you [[Using a Query to Combine Fields From Different Tables|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 [[Using a Query to Combine Fields From Different Tables|All Sightings]] query, for example, the query returned only the '''<ins style="color: red; font-weight: bold; text-decoration: none;">AOU_ID</ins>''' fields that matched birds our fictional user has actually identified, not all the '''AOU_ID''' field values.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">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.</td><td> </td><td style="background: #eee; font-size: smaller;">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.</td></tr>
<tr><td colspan="2" align="left"><strong>Line 16:</strong></td>
<td colspan="2" align="left"><strong>Line 16:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#: '''DateTaken''' from the '''Bird_Pictures''' table</td><td> </td><td style="background: #eee; font-size: smaller;">#: '''DateTaken''' from the '''Bird_Pictures''' table</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#: '''Location''' from '''Field_Trips''' table.</td><td> </td><td style="background: #eee; font-size: smaller;">#: '''Location''' from '''Field_Trips''' table.</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">#In the section of the {{Window|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.</td><td>+</td><td style="background: #cfc; font-size: smaller;">#In the section of the {{Window|Query Design}} window where the tables are shown, you should notice a line connecting '''PicFieldTripID''' in the '''Bird_Pictures''' table and '''FieldTrip_ID<ins style="color: red; font-weight: bold; text-decoration: none;">'</ins>'' 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.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Click on the connecting line so it turns blue.</td><td> </td><td style="background: #eee; font-size: smaller;">#Click on the connecting line so it turns blue.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Double click on the selected line. The {{Window|Join Properties}} window opens.</td><td> </td><td style="background: #eee; font-size: smaller;">#Double click on the selected line. The {{Window|Join Properties}} window opens.</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">#In the {{Section|Options}} section of the window, there is a {{prefName|Type}} drop-down that is currently set for {{prefName|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 {{Section|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 {{Section|Options}} section as you change join types.</td><td>+</td><td style="background: #cfc; font-size: smaller;">#In the {{Section|Options}} section of the window, there is a {{prefName|Type}} drop-down that is currently set for {{prefName|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 {{Section|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<ins style="color: red; font-weight: bold; text-decoration: none;">, </ins>appears in the {{Section|Options}} section as you change join types.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Assuming that the '''PicFieldTrip_ID''' field is in the left-hand column, select {{prefName|Left join}} as the join type.</td><td> </td><td style="background: #eee; font-size: smaller;">#Assuming that the '''PicFieldTrip_ID''' field is in the left-hand column, select {{prefName|Left join}} as the join type.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#To test the results of our query, click on the {{Button|Run Query}} icon on the toolbar. (The data sources icon with the green arrow).</td><td> </td><td style="background: #eee; font-size: smaller;">#To test the results of our query, click on the {{Button|Run Query}} icon on the toolbar. (The data sources icon with the green arrow).</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">#Save the Query by clicking on the {{Button|Save}} icon on the tool bar or pressing Command-S. You will be prompted to name the Query. I called mine '''Bird_Pictures_Locations'''.</td><td>+</td><td style="background: #cfc; font-size: smaller;">#Save the Query by clicking on the {{Button|Save}} icon on the tool bar or pressing <ins style="color: red; font-weight: bold; text-decoration: none;">{{Key|</ins>Command-S<ins style="color: red; font-weight: bold; text-decoration: none;">}}</ins>. You will be prompted to name the Query. I called mine '''Bird_Pictures_Locations'''.</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;"><br></ins></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">==Using the Query-in-a-Query Approach to Combine More than Two Tables==</td><td> </td><td style="background: #eee; font-size: smaller;">==Using the Query-in-a-Query Approach to Combine More than Two Tables==</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">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)</td><td> </td><td style="background: #eee; font-size: smaller;">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)</td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_An_Outer_Join_in_a_Query&diff=12467&oldid=prevLorinda: /* Using the Query-in-a-Query Approach to Combine More than Two Tables */2007-10-27T03:07:15Z<p><span class="autocomment">Using the Query-in-a-Query Approach to Combine More than Two Tables</span></p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 03:07, 27 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 28:</strong></td>
<td colspan="2" align="left"><strong>Line 28:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">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)</td><td> </td><td style="background: #eee; font-size: smaller;">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)</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">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).</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">===Determine the Sides of the Central Query===</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">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.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">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:</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">:Bird Side: AOU_ID, BirdPictured, PicFieldTrip_ID, DateTaken</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">:Field Trips Side: FieldTrip_ID, Location, StateOrProvince</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">'''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.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">===Create the Side Queries===</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">'''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]]</td><td> </td><td style="background: #eee; font-size: smaller;">'''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]]</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#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.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Create a query name '''FieldTrips_Locations'''. Include the '''FieldTrip_ID, Location''', and '''StateOrProvince''' fields from the '''Field_Trips''' and '''Locations''' tables.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">===Create the Central Query===</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Create a new query named '''Pix_Locations'''</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Add the '''BirdPix_AOU_ID''' and '''FieldTrips_Locations''' queries</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Add the following fields to the columns in the bottom of the {{Window|Query Design}} window:</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:AOU_ID</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:BirdPictured</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:DateTaken</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:Location</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:StateOrProvince</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#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.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Double click on this line to open the {{Window|Join properties}} window.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#In the {{Section|Options}} section, set the correct type of join in the {{prefName|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 {{Window|Query Design}} window. You want all the records from the '''BirdPix_AOU_ID''' "table." When I created this query, that meant a right join.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">===Notes===</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">*You can add filtering criteria to either side join, or to the central join. See [[Filtering in Queries and Tables]].</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">*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.</td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_An_Outer_Join_in_a_Query&diff=12466&oldid=prevLorinda: begin QiQ section2007-10-27T02:20:45Z<p>begin QiQ section</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 02:20, 27 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 5:</strong></td>
<td colspan="2" align="left"><strong>Line 5:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">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.</td><td> </td><td style="background: #eee; font-size: smaller;">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.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">==Using an Outer Join to Connect Two Tables==</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Click on the {{prefName|Queries}} icon in the left hand column of the main database document window.</td><td> </td><td style="background: #eee; font-size: smaller;">#Click on the {{prefName|Queries}} icon in the left hand column of the main database document window.</td></tr>
<tr><td colspan="2" align="left"><strong>Line 23:</strong></td>
<td colspan="2" align="left"><strong>Line 25:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Save the Query by clicking on the {{Button|Save}} icon on the tool bar or pressing Command-S. You will be prompted to name the Query. I called mine '''Bird_Pictures_Locations'''.</td><td> </td><td style="background: #eee; font-size: smaller;">#Save the Query by clicking on the {{Button|Save}} icon on the tool bar or pressing Command-S. You will be prompted to name the Query. I called mine '''Bird_Pictures_Locations'''.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">'''Note:''' This </del>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. <del style="color: red; font-weight: bold; text-decoration: none;">Using outer joins with more than two tables </del>can <del style="color: red; font-weight: bold; text-decoration: none;">probably be done by creating the Query </del>in SQL view <del style="color: red; font-weight: bold; text-decoration: none;">(which requires knowledge </del>of SQL<del style="color: red; font-weight: bold; text-decoration: none;">) or </del>by using the Query in a Query <del style="color: red; font-weight: bold; text-decoration: none;">technique</del>. <del style="color: red; font-weight: bold; text-decoration: none;">This tutorial will hopefully be expanded to include directions </del>for <del style="color: red; font-weight: bold; text-decoration: none;">one or more of these techniques </del>in the <del style="color: red; font-weight: bold; text-decoration: none;">near future</del>.</td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">==Using the Query-in-a-Query Approach to Combine More than Two Tables==</ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">The above </ins>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. <ins style="color: red; font-weight: bold; text-decoration: none;">Individuals proficient in SQL </ins>can <ins style="color: red; font-weight: bold; text-decoration: none;">create correctly ordered SELECT statements using LEFT or RIGHT JOINS </ins>in SQL view<ins style="color: red; font-weight: bold; text-decoration: none;">. For those </ins>of <ins style="color: red; font-weight: bold; text-decoration: none;">us less comfortable with </ins>SQL<ins style="color: red; font-weight: bold; text-decoration: none;">, it is also possible to achieve the desired results </ins>by using the Query<ins style="color: red; font-weight: bold; text-decoration: none;">-</ins>in<ins style="color: red; font-weight: bold; text-decoration: none;">-</ins>a<ins style="color: red; font-weight: bold; text-decoration: none;">-</ins>Query <ins style="color: red; font-weight: bold; text-decoration: none;">approach</ins>. <ins style="color: red; font-weight: bold; text-decoration: none;">(Sometimes called QiQ </ins>for <ins style="color: red; font-weight: bold; text-decoration: none;">short)</ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"> </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">'''Note:''' Because creating Queries has been covered </ins>in <ins style="color: red; font-weight: bold; text-decoration: none;">detail earlier in this tutorial series, </ins>the <ins style="color: red; font-weight: bold; text-decoration: none;">instructions below presume that the reader is familiar with the basics of creating queries</ins>. <ins style="color: red; font-weight: bold; text-decoration: none;"> Those who are not should first read [[Using a Query to Combine Fields From Different Tables]]</ins></td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_An_Outer_Join_in_a_Query&diff=12465&oldid=prevLorinda: add instruction to save the query2007-10-27T02:08:20Z<p>add instruction to save the query</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 02:08, 27 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 21:</strong></td>
<td colspan="2" align="left"><strong>Line 21:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#To test the results of our query, click on the {{Button|Run Query}} icon on the toolbar. (The data sources icon with the green arrow).</td><td> </td><td style="background: #eee; font-size: smaller;">#To test the results of our query, click on the {{Button|Run Query}} icon on the toolbar. (The data sources icon with the green arrow).</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Save the Query by clicking on the {{Button|Save}} icon on the tool bar or pressing Command-S. You will be prompted to name the Query. I called mine '''Bird_Pictures_Locations'''.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">'''Note:''' This 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. Using outer joins with more than two tables can probably be done by creating the Query in SQL view (which requires knowledge of SQL) or by using the Query in a Query technique. This tutorial will hopefully be expanded to include directions for one or more of these techniques in the near future.</td><td> </td><td style="background: #eee; font-size: smaller;">'''Note:''' This 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. Using outer joins with more than two tables can probably be done by creating the Query in SQL view (which requires knowledge of SQL) or by using the Query in a Query technique. This tutorial will hopefully be expanded to include directions for one or more of these techniques in the near future.</td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_An_Outer_Join_in_a_Query&diff=12464&oldid=prevLorinda: write article2007-10-27T02:06:11Z<p>write article</p>
<p><b>New page</b></p><div>{{TBBA Header}}<br />
<br />
<br />
By default, Base uses Inner Joins when you [[Using a Query to Combine Fields From Different Tables|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 [[Using a Query to Combine Fields From Different Tables|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.<br />
<br />
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.<br />
<br />
#Click on the {{prefName|Queries}} icon in the left hand column of the main database document window.<br />
#Click on the {{prefName|Create Table in Design View}} line in the {{Section|Tasks}} name. The {{Window|Query Design}} window opens, as will the {{Window|Add Table or Query}} window.<br />
#In the {{Window|Add Table or Query}} window, add the '''Bird_Pictures''' and '''Field_Trips''' tables by clicking each one in turn and clicking {{Button|Add}}.<br />
#Close the {{Window|Add Table or Query}} window.<br />
#Add the following fields from the tables to the columns in the bottom section of the {{Window|Query Design}} window: (See [[Using a Query to Combine Fields From Different Tables|this tutorial]] if you aren't sure how to do so.)<br />
#: '''BirdPictured''' from the '''Bird_Pictures''' table<br />
#: '''DateTaken''' from the '''Bird_Pictures''' table<br />
#: '''Location''' from '''Field_Trips''' table.<br />
#In the section of the {{Window|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.<br />
#Click on the connecting line so it turns blue.<br />
#Double click on the selected line. The {{Window|Join Properties}} window opens.<br />
#In the {{Section|Options}} section of the window, there is a {{prefName|Type}} drop-down that is currently set for {{prefName|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 {{Section|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 {{Section|Options}} section as you change join types.<br />
#Assuming that the '''PicFieldTrip_ID''' field is in the left-hand column, select {{prefName|Left join}} as the join type.<br />
#Click {{Button|OK}}<br />
#To test the results of our query, click on the {{Button|Run Query}} icon on the toolbar. (The data sources icon with the green arrow).<br />
<br />
'''Note:''' This 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. Using outer joins with more than two tables can probably be done by creating the Query in SQL view (which requires knowledge of SQL) or by using the Query in a Query technique. This tutorial will hopefully be expanded to include directions for one or more of these techniques in the near future.</div>Lorinda