Using an SQL INNER JOIN with OleDbDataReader in C#

Question

I am using the OleDbDataReader to access an Access Database with 3 tables. The database contains the main "Membership" table with a column ID, and a "Payment" and "Boat" table both using the ID as a foreign key called PID and BID respectively.

The following SQL query runs correctly and returns the correct values from the Membership and Payment tables.

SELECT * 
  FROM Membership 
INNER JOIN Payment ON Membership.ID = Payment.PID;

Whereas the following SQL query generates an error when trying to access all 3 tables:

SELECT * 
  FROM Membership 
INNER JOIN Payment ON Membership.ID = Payment.PID 
INNER JOIN Boat ON Membership.ID = Boat.BID;

Error: Additional information: Syntax error (missing operator) in query expression 'Membership.ID = Payment.PID INNER JOIN Boat ON Membership.ID = Boat.BI'.

Due to the error I am starting to wonder whether the OleDbDataReader is capable of executing two INNER JOINs as it asks for a missing operator, however I have tried all the usual ones and cannot seem to get the query to run correctly. Any help would be greatly appreciated :)

Answer 1

MS Access requires additional parentheses for multiple joins:

SELECT *
FROM (Membership INNER JOIN
      Payment
      ON Membership.ID = Payment.PID
     ) INNER JOIN
     Boat
     ON Membership.ID = Boat.BID;

You should also explicitly list the columns that you want to avoid duplicate column names.