SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 ?
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    @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

  4. #4
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland

  5. #5
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ramasaig View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    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?

  8. #8
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by ramasaig; Dec 8, 2012 at 16:42. Reason: Add quote
    Tim Dawson
    Isle of Mull, Scotland

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Rudy, I'll follow those up.
    Tim Dawson
    Isle of Mull, Scotland

  11. #11
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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'.
    Last edited by ramasaig; Dec 9, 2012 at 19:17. Reason: improve display of code
    Tim Dawson
    Isle of Mull, Scotland

  12. #12
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland

  13. #13
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ramasaig View Post
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland

  16. #16
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland

  19. #19
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    pleasure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •