MS Access – How to Join 3 Tables Using SQL Query

ms access

I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three tables.

This is my query

SELECT A.Name, B.Quantity, C.Quantity 
FROM A 
LEFT OUTER JOIN B 
    ON A.ID = B.ID 
LEFT OUTER JOIN C 
    ON B.No = C.No;

Best Answer

MS-Access is rather picky at how it wants the joins to be formed. Add parentheses:

SELECT A.Name, B.Quantity, C.Quantity 
FROM 
  ( A LEFT OUTER JOIN B ON A.ID = B.ID )
  LEFT OUTER JOIN C ON B.No = C.No ;

Standard SQL syntax - and most other DBMS - do not need require parentheses in the FROM clause, although you are allowed to use them for clarity.

Related Question