ACC: Outer Join with WHERE Clause Returns Unexpected Records

Last reviewed: July 18, 1997
Article ID: Q124152
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run a query based on two tables (in this example, called table A and table B) with an outer join to include all the records from table A and apply a WHERE clause to table B, only those records from table B that have a matching value in table A are returned. This seems to be the reverse of what you expected.

CAUSE

When you run the query, the outer join is performed first, creating all the records from table A. Then, the WHERE clause from table B is applied to all the records, eliminating records from the query. The result set does not contain all the records from table A, but contains only those where the condition is met for table B.

Unlike Microsoft Access, SQL Server currently processes the WHERE before the Join. This is because they implemented this feature before the ANSI SQL-92 standard, and before then, there was no outer join spec at all. Therefore, the same query could return different results against SQL Server depending on whether you use a Select query or a SQL pass-through query (in which case SQL Server would handle the query and simply return the results.)

RESOLUTION

If you want to return all the records in table A, you can use two queries. The first query should apply the WHERE clause to table B, and the second query should combine table A with the first query to perform the outer join.

STATUS

This type of query processing is by design and complies with the ANSI SQL 92 specification.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

  2. Create a new query based on the Categories and the Products tables. The two tables should be joined on the CategoryID field (or Category ID in versions 1.x and 2.0).

  3. Double-click the join line. In the Join Properties box, select the "Include ALL records from 'Categories'..." option button, and then click OK.

  4. Drag the CategoryName field (or Category Name in versions 1.x and 2.0) from the Categories field list to the first column of the query grid.

  5. Drag the ProductName field (or Product Name in versions 1.x and 2.0) field from the Products field list to the second column of the query grid.

  6. In the Criteria row of the ProductName column, type "Like A*" (without the quotation marks).

  7. Run the query. Note that not all the records from the Categories table are returned. Instead, only those records with a Product Name starting with the letter "A" are returned.

Steps to Return the Expected Recordset

  1. Open the sample database Northwind.mdb.

  2. Create a new query based on the Products table.

  3. Drag the CategoryID and ProductName fields from the Products field list to the query grid.

  4. In the Criteria row for the ProductName field, type "Like A*" (without the quotation marks).

  5. Save the query as Query1, and then close it.

  6. Create a new query based on the Categories table and the Query1 query.

  7. Drag the CategoryName field from the Categories field list to the query grid.

  8. Drag the ProductName field from the Query1 field list to the query grid.

  9. Double-click the join line. In the Join Properties box, select the "Include ALL records from 'Categories'..." option button, and then click OK.

  10. Run the query. Note that all the records from the Categories table are returned, with the ProductName field filled in for records with a matching record in the Products table.


Keywords : kbusage QryJoin
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 18, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.