SELECTing for something that isn't in the table (or WHERE NOT...)
I'm doing a feasibility study for an accommodation booking database. I have two tables as shown below (there are some other fields in the 'rooms' table, which I've omitted here).
In my prototype database I have just 15 rooms. The bookings table contains many entries (about 210, covering January 2013 only), each linking a specific room to a date when it is booked. The partial 'bookings' table looks like this:
I can readily find out which rooms are booked on a certain date with a query like this:
But I haven't yet found a way to determine which rooms are NOT booked, via a single query. Using something like
SELECT r.room_id FROM `rooms` AS r INNER JOIN `bookings` AS b ON r.room_id = b.room_id WHERE b.book_date = '2013-01-14'
selects all the rooms, because they all have at least one booking which is not on that date.
WHERE book_date != '2013-01-14'
Having found the booked rooms I could get the free ones by subtraction, but surely there's a better way ?
Eventually I need to find rooms that are free for several consecutive days.
Can anyone help, please ?