As I was writing a report using VBA in MS-Access, I needed to get a name from a table. Yet, once in a while, that name does not exist in that table (for good reasons) and yet I'd like a more human name than the default number that you otherwise get... The result was a need for two LEFT JOIN in my SQL command.

1. Using the result

I have three text boxes in my form:

(a) The one that the user sees with the valid result, it actually includes a simple VBA script:

=iif([text2]<>"",[text2],[text3])

(b) The one with the name when it exist

(c) The one with the fallback name

2. The SQL command

Here I want to get text2 and text3 (among other fields). These come from table2 when a specified field matches or table3 if the test with table2 failed (although the SQL will return a text3 if one exists even if table2 had a good answer.)

SELECT text2, text3, ...

FROM (table1 LEFT JOIN table2 ON table1.field = table2.field) LEFT JOIN table3 ON table1.field = table3.field

WHERE table1.field = 'some value'

As you can see, this contradicts the MS-Access documentation that says you cannot use multiple LEFT JOIN (or RIGHT JOIN if that matter) within an SQL statement without using an INNER JOIN so you can have nested LEFT or RIGHT JOIN.

Stronger database systems such as PostgreSQL support multiple joins in a row without any specific syntax.

LEFT JOIN, RIGHT JOIN documentation at Microsoft.

See also this page about the LEFT JOIN not supported error.