LEFT and RIGHT join collision in MS Access (Error 3258)

"Ambiguous outer joins" (Error 3258) in MS Access (v.2010)

While was trying to extract all SQL queries from MS Access database hit very unusual problem:









Run-time error '3258':
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement"

I've found the Error description from Microsoft: https://msdn.microsoft.com/en-us/library/bb209755%28v=office.12%29.aspx?f=255&MSPPError=-2147217396
However it was not helpful, because I did not really tried to run the query, I've just simply tried to read the script. That meant I couldn't follow Microsoft's instructions and correct the query.

Then I've started my own research and here are the test case results:

In MS Access:


1.       Create new MS Access DB.
2.       Create new “Table1” with following values

3.       Create new “Table2” with following values

4.       Create “Query1” and add both tables to it. Choose all fields to be selected.

5.       Drag “Field1” from “Table1” to “Field1” in “Table2”. Click on the connection and specify 2^nd option. Then press “OK”

6.       Drag “Field2” from “Table2” to “Field2” in “Table1”. Click on the connection and specify 3rd option. Then press “OK”

7.       Now picture has to be like this:

8.       If you try to view query results it will return following

9.       If you try to view SQL code of the query you get following error:

10.   Explanation:

First "JOIN" operator tries to do following:
FROM Table1  LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1

Second "JOIN" operator tries to do this:
FROM Table2 RIGHT JOIN Table1 ON Table2.Field2 = Table1.Field2;

Both statements “Table1  LEFT JOIN Table2” and “Table2 RIGHT JOIN Table1” are almost identical and MS Access treats them identically and correctly.

However it does not do a conversion in any way and it can’t represent a SQL query with both tables joined LEFT and RIGHT at the same time. 

Lets try to look at it from SQL Server perspective.
Go to SQL Server Management Studio to local SQL Server on your own workstation or on Dev/Test Server. (NEVER try it in Production)

Here is how it works in SQL Server:

use master GO CREATE DATABASE TestDB GO USE TestDB GO WITH SRC as (SELECT 1 as F1, 'A' as F2 UNION SELECT 2,'B' UNION SELECT 3,'C') SELECT * INTO Table1 FROM SRC; GO WITH SRC as (SELECT 1 as F1, 'A' as F2 UNION SELECT 2,'C') SELECT * INTO Table2 FROM SRC; GO SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON Table1.F1 = Table2.F1 GO SELECT Table1.*, Table2.* FROM Table2 RIGHT JOIN Table1 ON Table2.F2 = Table1.F2; GO

Now will try to Merge “LEFT” and “RIGHT”

SELECT * FROM Table1, Table2 WHERE Table1.F1 *= Table2.F1 and Table2.F2 =* Table1.F2; GO

As a result of using old syntax we get an error:

Msg 4147, Level 15, State 1, Line 2 The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

The error clearly describes the problem. Only SQL Server 2000 supports Non-ANSI join syntax

If we try to switch our Test database in old compatibility mode everything will be “fine”:

ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 80 GO SELECT * FROM Table1, Table2 WHERE Table1.F1 *= Table2.F1 and Table2.F2 =* Table1.F2; GO

I put word “fine” in quotations because it is NOT fine. You really have to do the following (one way or another):

SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON Table1.F1 = Table2.F1 and Table2.F2 = Table1.F2; GO SELECT Table1.*, Table2.* FROM Table2 RIGHT JOIN Table1 ON Table1.F1 = Table2.F1 and Table2.F2 = Table1.F2; GO

That is only the RIGHT way, which produces correct results.

At the end of the exercise do not forget to clean after yourself:

use master GO DROP DATABASE TestDB; GO

As a conclusion I’d advise anybody, who is doing Drag-n-Drops in MS Access to link tables within a query only from one side, not from both. 

And learn SQL of cause.