ACC: Outer Join with WHERE Clause Returns Unexpected Records (124152)
The information in this article applies to:
- Microsoft Access 1.0
- Microsoft Access 1.1
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
This article was previously published under Q124152 Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
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 you 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.
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.
Modification Type: | Major | Last Reviewed: | 5/6/2003 |
---|
Keywords: | kbprb KB124152 |
---|
|