CODEWITHSUNDEEP
mysql
Shooresh
Shooresh

Reputation: 105

What's wrong with this mySQL logic?

In table "Booking" I hold details of a customer's booking (date/time/RowNumber). In table "Seat" I hold details of all seats (RowNumber and Zone) in the theater.

The purpose of this query is to join Seat to Booking to see which seats are taken by displaying those rows in Seat which have a null entry in the corresponding Booking RowNumber. Here is the code:

SELECT s.Zone, s.RowNumber 
FROM Booking b JOIN Seat s 
ON s.RowNumber = b.RowNumber 
WHERE b.PerfDate = '2016-12-12' 
AND b.PerfTime = '20:30:00' 
AND b.RowNumber is null;

The code is accepted but the result comes back as an empty set with the last AND statement or just shows what is in bookings without it. There is clearly something wrong with the logic I am using but I cannot pinpoint it as what I am trying to do makes sense when I read it. Probably an obvious mistake but some help would be appreciated.

Thank you in advance.

[Edit - I have spotted the logic error... it is the fact that I am asking for specific date and time when the records I want don't have those by definition but no idea how to get round it, seems like catch 22.]

Upvotes: 0

Views: 43

Answers (1)

Barmar
Barmar

Reputation: 780949

You need to use a LEFT JOIN, and put all the criteria for the non-matching row into the ON clause.

SELECT s.zone, s.RowNumber
FROM Seat AS s
LEFT JOIN Booking AS b
ON s.RowNumber = b.RowNumber AND b.PerfDate = '2016-12-12' AND b.PerfTime = '20:30:00'
WHERE b.RowNumber IS NULL

Upvotes: 2

Related Questions