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:
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'
But I haven’t yet found a way to determine which rooms are NOT booked, via a single query. Using something like
WHERE book_date != '2013-01-14'
selects all the rooms, because they all have at least one booking which is not on that date.
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.
SELECT r.room_id
FROM rooms AS r
[COLOR="#0000FF"]LEFT OUTER[/COLOR]
JOIN bookings AS b
ON b.room_id = r.room_id
[COLOR="#0000FF"]AND [/COLOR]b.book_date = '2013-01-14'
[COLOR="#0000FF"]WHERE b.room_id IS NULL [/COLOR]
Kyle, I understand why you say that, but this was a pure SQL question, and I felt it would be better here. Your help yesterday is much appreciated, and with Rudy’s input I really feel I’m on track now.
Thank you, Rudy, it works a treat (of course).
Now I need to make it work so that I can find rooms that are available for several days. I’ll try to work out how to do that myself, but I may be back if stuck.
I’ll also read up about Joins in my PDF copy of ‘SimplySQL’. I didn’t find it until I went to the Index (which I should have done sooner, of course). Give me a printed book every time.
Good question ! I’m learning on the job here, as this is much the most complicated thing I’ve ever tried in MySQL.
As you say, the table is currently set up for one row per day. That’s a by-product of the PHP script I wrote to convert a spread-sheet style table (with date columns like a calendar) into the present ‘bookings’ table.
If it can be done without introducing additional complications I’d actually prefer a table with start date and number of days (nights).
In my experience (my wife and I run a B&B) most prospective guests plan to come on (say) Monday 10th and stay three nights rather than come on 10th and leave on 13th. All comes to the same thing, of course.
As discussed above I’ve amended my ‘bookings’ table to show start and end dates for bookings and created a ‘Select’ statement that successfully finds rooms that DO NOT have a booking starting or ending between two dates.
SELECT r.room_id
FROM rooms AS r
LEFT OUTER JOIN bookings2 AS b
ON r.room_id = b.room_id
AND (b.start_date BETWEEN '2013-01-14' AND '2013-01-16')
AND (b.end_date BETWEEN '2013-01-14' AND '2013-01-16')
WHERE b.room_id is NULL
Unfortunately this can’t cope with bookings that start before and end after the chosen dates. (Perhaps that’s what you meant by ‘crossover bookings’, Kyle ?). For example a room booked from ‘2013-01-12’ to ‘2013-01-18’ (six nights) would appear vacant to this search.
I can’t at present see any alternative to reverting to the earlier table which showed every booked date. I’ll have to add a ‘booking_id’ column that would tie a group of dates together as one booking, otherwise they’ll all look like single-night bookings. This ID would not be unique in this table, but there’ll be another table ‘booking_info’ (say) where further details of each booking would be contained in one record under the same ‘booking_id’.
SELECT r.room_id
FROM rooms AS r
LEFT OUTER JOIN bookings2 AS b
ON r.room_id = b.room_id
AND (b.start_date BETWEEN '2013-01-14' AND '2013-01-17'
OR (b.start_date < '2013-01-14' AND b.end_date > '2013-01-17'))
WHERE b.room_id is NULL
I did follow up your links, and looked hard at the one like you are quoting above. It was that which gave me the idea for my SELECT statement in post #13, which is working OK for me so far, but I accept needs further testing in case my current results are atypical (due to particular choice of dates/bookings in my test ‘bookings2’ table). Looking at it again, it seems it may not be dealing with case 2, although the statement in post #11 should have done. I need to revisit my test data and ensure all cases are present.
I think it’s best that I spend some time digesting what I’ve got so far, and ensuring that I fully understand it. If I can’t arrive at something that works under all conditions, I’ll be back.
Hello Rudy, I thought I should report back and say that I’ve found the following works as far as I can see:
START END
'2013-01-14' '2013-01-16'
| |
1 start_date---end_date | |
| |
2 start_date--|--end_date |
| |
3 | start_date---end_date |
| |
4 start_date--|-------------------------|--end_date
| |
5 | start_date--|--end_date
| |
6 | | start_date---end_date
SELECT r.room_id
FROM rooms3 AS r
LEFT OUTER JOIN bookings3 AS b
ON r.room_id = b.room_id
AND (b.start_date BETWEEN '2013-01-14' AND '2013-01-15' /* Finds case 3 & 5 */
OR b.end_date BETWEEN '2013-01-14' AND '2013-01-15' /* Finds case 2 & 3 */
OR (b.start_date < '2013-01-14' AND b.end_date > '2013-01-15')) /* Finds case 4 */
WHERE r.rm_type = 'dbl' AND b.room_id is NULL /* Inverts result */
/* cases 1 & 6 do not need to be found */
There were flaws in my earlier attempts. I have tested with a larger database than before, and it seems to give reliable results. In the particular case above I’ve isolated all the relevant bookings for all the properties, by hand, to show that only the correct ones were found (and none omitted).
Because the bookings are for night-time accommodation, it is possible for a new booking to start on the same day as the previous guests depart. I found it easier to define ‘end_date’ as the previous day (i.e. ‘last_night’) than to work out where to add or subtract 1 from the variables. So the search above is for two nights accommodation in a double room.
the query i gave you will find all cases 2 through 5 rather simply (as compared to your various ORs)
just use LEFT OUTER JOIN with IS NULL to reverse it
SELECT r.room_id
FROM rooms AS r
LEFT OUTER
JOIN bookings2 AS b
ON b.room_id = r.room_id
AND b.end_date >= '2013-01-14' /* eliminates case 1 */
AND b.start_date <= '2013-01-16' /* eliminates case 6 */
WHERE r.rm_type = 'dbl'
AND b.room_id is NULL
Thank you. Your code is certainly simpler. When I read it previously I got it into my head that it would only pick up the ‘booked’ rooms, so that there wouldn’t be any NULL results to reverse to. However, I’ve tried it, and it certainly picks up the NULL rooms.
Something’s still nagging at me, though. What happens if a room doesn’t yet have any bookings (at any point in the year) ? I worry it won’t get picked up. Perhaps there are two new ‘cases’. Case 0 where there are no bookings before, and case 7 where there are no bookings after. This condition is likely when setting up for another year (2014, 2015) where as yet there are no bookings.
My test ‘bookings’ table was constructed ‘sort-of’ randomly. Random first booked date in the year (within Jan-Mar), followed by random length (up to 7 nights, biased towards 2 or 3) followed by random ‘gap’ to next booking (0-7) and so on, with random cut-off date towards the year-end (within Oct-Dec). What it did NOT do was to omit any rooms, so there won’t actually be any with no bookings. I need to correct that and do some more testing.
This also raises the question of how I run the booking system seamlessly over a year-end. But I think I’ll leave that until I’ve sorted the current issue. Tourism on Mull is a seasonal business, and mostly occurs April-September, though there are always exceptions. And of course there are other places in the world.
Following my concerns above, I have deliberately removed one room from the bookings table, and run both ‘your’ code and ‘mine’ on the resulting data set. The results are identical. The room is included. So I need not have worried.
Given that your version is simpler, it would be silly not to use it. The discussion has much improved my understanding of JOINS. I need to write a hundred lines: “A LEFT OUTER JOIN returns all the rows from the LEFT table”.
