Retrieving only available seats?

I am confused about how I could return only the seats that have not already been booked.

The table schema I have been given

Stores 2 venues of seat information in the same table (50 seats each)

I then have a SeatBooking table which takes the seatNumber Venue and Booking number to make it unique to a performance.

The idea have seen a lot is: Create a field called SeatStatus with a boolean data type set 开发者_开发技巧to True if Taken and False if available. Then do the query return all where SeatStatus = false.

Can this be done in this situation?

This isn't specific to the language you're doing, but I would use an SQL LEFT JOIN to do this.

Basically this type of join says that you know the left side is present, (In this case, the seating chart) but the right side (The booking information) might not be, so you may get nulls. Left Join the Seat Information table to the Seat Booking table, selecting the performance you're looking up information for. As a final condition, check if the booking information is null, because if it is, the seat has not been reserved.

This is quite fast if your SQL tables are optimized the way they should be, and you'll get as a result, ONLY unreserved seats, so you don't need to do any filtering in code, and can have the SQL server do the heavy lifting for you. (Which is generally the most efficient way to do this)

The index you need to make this fast is on Seat Number and Performance on the Booking Information table.

Here's a general form of this query:

SELECT seat.* FROM seating seat LEFT JOIN bookings book ON seat.number = book.seat_number,book.performance = 'SOME PERFORMANCE ID' WHERE seat.venue = 'SOME VENUE' AND book.performance is null;

Try this without the 'AND book.performance is null' first, and selecting the booking table as well to make sure you're getting all the data, and, importantly, only one row per seat.