0

The question is: You want to be the first to book and pick seats for a flight. Find the flight_num and date of all flights for which there are no reservations.

From the following tables:

  • Flights (flight_num, source_city, dest_city)
  • Departures (flight_num, date, plane_type)
  • Passengers (passenger_id, passenger_name, passenger_address)
  • Bookings (passenger_id, flight_num, date, seat_number)

My answer was: 

SELECT D.flight_num, D.date
FROM DEPARTURES D, BOOKINGS B
WHERE B.passenger_id = NULL

I know this is wrong, but can anyone tell me why? What is the answer to this?

user3792136
  • 1
  • 1
  • 6
    Among other things, you want `is null` to check for null values. – shree.pat18 Oct 08 '14 at 18:06
  • 5
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged. Right now, without **any** join condition, you basically get a **cartesian product** - each row in `Departures` combined with each row in `Bookings` - most likely **not** what you need! – marc_s Oct 08 '14 at 18:07
  • 3
    Have you learned this in a course or from a book? If so, time to upgrade or get your money back. I think those old style joins were invented by the Romans. – GolezTrol Oct 08 '14 at 18:08
  • Furthermore you are cross joining now. You will need to state which fields are related to which in order to properly join the tables. `WHERE d.flight_num = b.flightnum` for instance – JNevill Oct 08 '14 at 18:09
  • See [this explanation of joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) – juergen d Oct 08 '14 at 18:10

2 Answers2

1

This might be better:

SELECT D.flight_num, D.date
FROM DEPARTURES D JOIN BOOKINGS B ON D.flight_num = B.flight_num 
WHERE B.passenger_id IS NULL

I'm not sure if it would be possible to book, but not reserve a seat number. If it is, then you need to change the WHERE clause for that.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
0

This might work?

    SELECT D.flight_num, D.date
    FROM Departures D LEFT OUTER JOIN Bookings B
    WHERE B.passenger_id IS NULL
Guest1
  • 1