CODEWITHSUNDEEP
sqlms-accessleft-joininner-joinright-join
SPedraza
SPedraza

Reputation: 159

Join with Multiple Tables

I am getting a syntax error with the following problem and can't seem to figure out, hope you guys can help me!

I have these tables (they are populated):

enter image description here

I am trying to retrieve the first and last name of all the passengers scheduled in a certain flight number so what I have is this:

SELECT PassFName, PassLName
FROM Passenger
INNER JOIN PassID ON Passenger.PassID = Reservation.PassID
INNER JOIN FlightNum ON FlightNum.Reservation = FlightNum.ScheduledFlight
WHERE ScheduledFlight.FlightNum = [Enter Flight Number];

However, I am getting error:

enter image description here

Not sure why and I have also noticed in the last line it is misspelling FlightNum.ScheduledFlight. Any idea what am I doing wrong?

Thank you!

Upvotes: 1

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

MS Access has a strange syntax for joins. It requires parentheses around each JOIN pair. So:

SELECT PassFName, PassLName
FROM (Passenger INNER JOIN
      Reservation
     ) ON Passenger.PassID = Reservation.PassID INNER JOIN
     FlightNum
     ON FlightNum.Reservation = FlightNum.ScheduledFlight
WHERE ScheduledFlight.FlightNum = [Enter Flight Number];

Although other databases support this syntax, it is only required in MS Access.

Upvotes: 1

Wellspring
Wellspring

Reputation: 1340

Gordon's point is valid, but he's got his parentheses misplaced and missed the other big issues. This query is more than a little whacked, with table names and field names flip-flopped. Here's what I would guess would work...

SELECT
       PassFName
     , PassLName
FROM (
     Passenger
     INNER JOIN Reservation
        ON Passenger.PassID = Reservation.PassID
     )
INNER JOIN ScheduledFlight
    ON Reservation.FlightNum = ScheduledFlight.FlightNum
WHERE
     ScheduledFlight.FlightNum = [Enter Flight Number];

Upvotes: 2

Related Questions