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 :)

Gordon Linoff · Accepted Answer · 2017-03-25 12:07:11Z

2

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.

answered Mar 25, 2017 at 12:07

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Will Anderson Over a year ago

This worked thanks, the only reason I don't list the columns is because I have around 20 columns and it's a small enough amount to avoid duplicates while also a large amount to list out. Thanks :)

Collectives™ on Stack Overflow

Using an SQL INNER JOIN with OleDbDataReader in C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related