Friday, 18 September 2015

Multiple LEFT JOINs in MS Access Database

If you are joining related link tables in MS Access database as like SQL Server database, it will work fine for a single link table, but it will not work for more than one join query. The below single join query will work fine in both MS Access and SQL database.
SELECT A.ca, B.cb
FROM TableA AS A LEFT JOIN TableB AS B ON B.id = A.id

Join more than one table in MS Access

If you want to join more than one tables, the below sql like join query will work only in SQl database.
SELECT A.ca, B.cb, C.cc
FROM TableA AS A LEFT JOIN TableB AS B ON B.id = A.id
LEFT JOIN TableC AS C ON C.id = A.id
If you run the above query in MS Access database, you will get the error "Syntax error (missing operator) in query expression".

To make multiple joins work in MS Access database, we need to enclose join query with parentheses.
SELECT A.ca, B.cb, C.cc
FROM (TableA AS A LEFT JOIN TableB AS B ON B.id = A.id)
LEFT JOIN TableC AS C ON C.id = A.id

Join more than two or more tables in MS Access

Like the above query, we need to add parentheses for every join query to make multiple joins work in MS Access database.
SELECT A.ca, B.cb, C.cc,D.cd
FROM ((TableA AS A LEFT JOIN TableB AS B ON B.id = A.id)
LEFT JOIN TableC AS C ON C.id = A.id)
LEFT JOIN TableD AS D ON D.id = A.id

Advertisements
Advertisements

No comments:

Post a Comment