MS Acces Jet SQL Error: Join Expression not supported with multiple Join conditions

Question

I'm trying to run this SQL Expression in Access:

Select *
From ((TableA
    Left Join TableB
        On TableB.FK = TableA.PK)
    Left Join TableC
        On TableC.FK = TableB.PK)
    Left Join (SELECT a,b,c FROM TableD WHERE b > 1) AS TableD
        On (TableD.FK = TableC.PK AND TableA.a = TableD.a)

but it keeps getting error: Join-Expression not supported. Whats the problem? Sorry, im just starting with Jet-SQL and in T-SQL its all fine. Thanks

Answer 1

The issue is that the final outer join condition TableA.a = TableD.a will cause the query to contain ambiguous outer joins, since the records to which TableA is joined to TableD will depend upon the results of the joins between TableA->TableB, TableB->TableC and TableC->TableD.

To avoid this, you'll likely need to structure your query with the joins between tables TableA, TableB & TableC existing within a subquery, the result of which is then outer joined to TableD. This unambiguously defines the order in which the joins are evaluated.

For example:

select * from
(
    select TableA.a, TableC.PK from
    (
        TableA left join TableB on TableA.PK = TableB.FK
    )
    left join TableC on TableB.PK = TableC.FK
) q1
left join
(
    select TableD.a, TableD.b, TableD.c, TableD.FK from TableD 
    where TableD.b > 1
) q2 
on q1.a = q2.a and q1.PK = q2.FK

Answer 2

Consider relating every join to the FROM table to avoid having to nest relations.

SELECT *
FROM ((TableA
    LEFT JOIN TableB
        ON TableB.FK = TableA.PK)
    LEFT JOIN TableC
        ON TableC.FK = TableA.PK)
    LEFT JOIN 
          (SELECT FK,a,b,c
           FROM TableD WHERE b > 1
          ) AS TableD
        ON  (TableD.FK = TableA.PK)
        AND (TableD.a  = TableA.a)