SELECTing for something that isn't in the table (or WHERE NOT...)

**ramasaig** · Dec 8, 2012 13:18

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).

Code:

tbl_rooms
-------
room_id
property_id
room_type
....etc

tbl_bookings
------
room_id
book_date

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:

Code:

room_id	book_date
2043.01	01/01/2013
2043.01	03/01/2013
2043.01	06/01/2013
2043.01	07/01/2013
2043.01	14/01/2013
2043.01	20/01/2013
2043.01	21/01/2013
2043.01	22/01/2013
2043.01	23/01/2013
2043.01	24/01/2013
2043.01	26/01/2013
2043.01	29/01/2013
2043.01	30/01/2013
2043.01	31/01/2013
2043.02	01/01/2013
2043.02	03/01/2013
2043.02	07/01/2013
2043.02	19/01/2013
2043.02	21/01/2013

I can readily find out which rooms are booked on a certain date with a query like this:

Code:

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

Code:

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.

Can anyone help, please ?

**r937** · Dec 8, 2012 13:40

Code:

SELECT r.room_id 
  FROM rooms AS r 
LEFT OUTER 
  JOIN bookings AS b 
    ON b.room_id = r.room_id 
   AND b.book_date = '2013-01-14'
 WHERE b.room_id IS NULL

**K. Wolfe** · Dec 8, 2012 14:41

@ramasaig ;, I'd try to keep these questions in your original post so that people can see the start to finish problems you've had. http://www.sitepoint.com/forums/show...-may-not-exist

**ramasaig** · Dec 8, 2012 15:37

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.

**ramasaig** · Dec 8, 2012 15:44

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.

**r937** · Dec 8, 2012 16:02

Originally Posted by ramasaig

Now I need to make it work so that I can find rooms that are available for several days.

one key factor is how you propose to register bookings for a room for multiple days

looks like you might be set up to insert one row per day

another option is one row with a start date and end date

any thoughts on how you prefer to do it?

**K. Wolfe** · Dec 8, 2012 16:05

Originally Posted by r937

one key factor is how you propose to register bookings for a room for multiple days

looks like you might be set up to insert one row per day

another option is one row with a start date and end date

any thoughts on how you prefer to do it?

Interesting concept, how would you handle ensuring no cross bookings?

**ramasaig** · Dec 8, 2012 16:41

any thoughts on how you prefer to do it?

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.

Second choice would be Start date and end date.

**r937** · Dec 8, 2012 16:50

start date and end date actually make for fairly easy querying for availability

there are several previous threads here on overlapping date ranges, for example --
http://www.sitepoint.com/forums/showthread.php?t=733001
http://www.sitepoint.com/forums/showthread.php?t=664040

**ramasaig** · Dec 8, 2012 16:53

Thanks, Rudy, I'll follow those up.

**ramasaig** · Dec 9, 2012 19:14

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.

Code:

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'.

**ramasaig** · Dec 10, 2012 04:04

Since writing the above I have tried:

Code:

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

which (on first test) seems to work.

**K. Wolfe** · Dec 10, 2012 09:05

Originally Posted by ramasaig

Since writing the above I have tried:

Code:

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

which (on first test) seems to work.

This isn't correct, I'll have to revisit this later today though.

**r937** · Dec 10, 2012 11:15

the approach that i use, described in threads i linked to in post #9 above, searches for overlap like this --

Code:

                       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 rooms AS r
INNER 
  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 */

because it's an inner join, this query retrieves all rooms which have an overlap booking

thus, you can assign any of the rooms not listed for the requested date range

**ramasaig** · Dec 10, 2012 12:41

Thank you, Rudy.

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.

Thanks very much for your help to date.

**ramasaig** · Dec 15, 2012 11:26

Hello Rudy, I thought I should report back and say that I've found the following works as far as I can see:

Code:

                       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.

Thank you for your help.

**r937** · Dec 15, 2012 11:49

your re-write of the logic seems to be overkill

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

Code:

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

**ramasaig** · Dec 16, 2012 04:11

Hello Rudy,

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.

**ramasaig** · Dec 16, 2012 07:35

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".

Thank you for all your help.

**r937** · Dec 16, 2012 10:03

pleasure

User Tag List

Thread: SELECTing for something that isn't in the table (or WHERE NOT...)

Thread Tools

Display

SELECTing for something that isn't in the table (or WHERE NOT...)

Bookmarks

Bookmarks

Posting Permissions