Ambiguous left joins in MS Access

Question

I want to convert the following query from T-SQL

SELECT
    * 
FROM
    A                            LEFT JOIN
    B ON A.field1 = B.field1     LEFT JOIN
    C ON C.field1 = A.field2 AND
         C.field2 = B.field2

to Jet SQL.
Now MS Access does not accept ambiguous queries. How can I do that? I can't put the second comparison in the WHERE clause. Why? Because my scenario is that I am selecting records that does not exist in C.

How to select all records from one table that do not exist in another table?

Now, how do you that in MS Access? Thanks in advance for your time and expertise.

score 3 · Accepted Answer · answered Jul 31 '12 at 08:08

3

You need a derived table to make this work in MS Access:

SELECT * 
FROM (
   SELECT A.Field1, A.Field2 As A2, B.Field2 
   FROM A 
   LEFT JOIN B ON A.field1 = B.field1) AS x 
LEFT JOIN C ON  x.A2 = C.field1 AND  x.field2= C.field2

answered Jul 31 '12 at 08:08

Fionnuala

90,370
7
114
152

Ouch! It's so painful querying in MS Access! Chosen as the correct answer. – yoitsfrancis Jul 31 '12 at 08:12

Igor Borisenko · Answer 2 · 2012-07-31T10:27:52.080

0

From Help LEFT JOIN, RIGHT JOIN Operations

You can link multiple ON clauses. See the discussion of clause linking in the INNER JOIN topic to see how this is done.

You can also link several ON clauses in a JOIN statement, using the following syntax:

SELECT fields 
FROM table1 
     INNER JOIN table2 ON table1.field1 compopr table2.field1 
                          AND ON table1.field2 compopr table2.field2)
                          OR ON table1.field3 compopr table2.field3)];

But works this (it seems there is an error in help):

SELECT * 
FROM A 
     LEFT JOIN B ON A.field1 = B.field1
     LEFT JOIN C ON (C.field1 = A.field2 AND C.field2 = B.field2)

edited Jul 31 '12 at 10:27

answered Jul 31 '12 at 08:10

Igor Borisenko

3,806
3
34
49

Mine returns an error Syntax error (missing operator) in query expression ". – yoitsfrancis Jul 31 '12 at 08:19
Hi Igor, I would like to clarify if you've tested the script. Shouldn't there be a parenthesis with Tables B and C or A and B? – yoitsfrancis Aug 01 '12 at 02:56
@yoitsfrancis Yes, I've tested the script and parentheses are essential as in my last updated script. – Igor Borisenko Aug 01 '12 at 05:13
Hi Igor, it returns a JOIN expression not supported in my end – yoitsfrancis Aug 02 '12 at 01:57

Ambiguous left joins in MS Access

2 Answers2

Linked