Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2008
    Posts
    9

    Unanswered: Help with resevation query

    Im trying to develop a booking system and need help with the basic "available units" query.

    My tables:
    Code:
    TABLE `Facilities` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(150) NOT NULL,
      `price` double NOT NULL,
      `description` text NOT NULL,
      PRIMARY KEY  (`id`)
    )
    
    TABLE `Reservations` (
      `id` int(11) NOT NULL auto_increment,
      `customer_id` int(11) NOT NULL,
      `facility_id` int(11) NOT NULL,
      `reservation_from` datetime NOT NULL,
      `reservation_to` datetime NOT NULL,
      PRIMARY KEY  (`id`)
    )
    I would like to make a query that accepts 2 parameters "from date" and "to date". Based on these dates I need my query to return all available units/facilities within this period.

    Please any help is apreciated, if anyone gor improvements to my tables aswell dont hold back on informing me.

  2. #2
    Join Date
    Jun 2008
    Posts
    9

    Solved!

    Code:
    SELECT *
    FROM Facilities LEFT JOIN Reservations ON Facilities.id = facility_id AND ((reservation_from BETWEEN x AND y) OR (reservation_to BETWEEN x AND y))
    WHERE isNull(Reservations.id)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if reservation_from is less than x and reservation_to is greater than y, the facility is booked for a larger range than x to y, and is unavailable

    but your query would return it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2008
    Posts
    9
    Hmm.. I just tested it and it does not return any rows when the search range is greater than the reserved range. Think it is working!

    But I would love to hear your solution as well.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your search range is x to y?

    then a reservation which starts before x and ends after y is definitely not available, and yet your query returns it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2008
    Posts
    9
    Yes, you are right! I ahd to alter the query into this:

    Code:
    SELECT *
    FROM Facilities LEFT JOIN Reservations ON Facilities.id = facility_id AND ((reservation_from BETWEEN DATE('2008-06-18 14:00:00') AND DATE('2008-06-19 12:00:00')) OR (reservation_to BETWEEN DATE('2008-06-18 14:00:00') AND DATE('2008-06-19 12:00:00')))
    OR (
    (DATE('2008-06-18 14:00:00') BETWEEN reservation_from AND reservation_to)
    )
    OR (
    (DATE('2008-06-19 12:00:00') BETWEEN reservation_from AND reservation_to)
    )
    WHERE isNull(Reservations.id)
    But now my problem is adding multiple facilities.

    Done some tests:

    Reservation on facility 1 (no reservations on facility 2):
    From: 05.05.2005 To: 08.05.2005

    Search 1:
    From 03.05.05 - To 04.05.05 (Returns 2 rows - OK)

    Search 2:
    From 03.05.05 - To 06.05.05 (Returns 0 rows - Wrong)
    Should have returned 1 row with "Facility 2" since there are no reservations on "Facility 2" yet.

    Have I missed something?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have a look at this --
    http://www.dbforums.com/showthread.php?t=1626870

    if anything, at least the timeline diagram should help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2008
    Posts
    9
    Think I got it figured out now, the timeline did help!

    Code:
    SELECT * FROM Facilities LEFT JOIN Reservations ON Facilities.id = facility_id WHERE reservation_to <= #user_from_date# OR  reservation_from >= #user_to_date#
    Clean and easy, thank you for your help.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    should be an INNER JOIN, by the way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2008
    Posts
    9
    I have run into a problem with my query, until now I have tested with just 2 rows in my reservation table:

    ID: 1
    From: 2008-06-17
    To: 2008-06-20
    Facility: 1

    ID: 2
    From: 2008-06-24
    To: 2008-06-26
    Facility: 2

    My query works alright with just these 2 rows. The problem occurs when there are more than 2 rows in the reservation table.

    If I add another reservation:

    ID: 3
    From: 2008-06-28
    To: 2008-06-30
    Facility: 1

    And run my query with parameters "FROM 2008-06-22, TO 2008-06-23" i get this result:

    Facility (1, 2, 1)

    But what I wanted to see was just 1 and 2.

    Same happens when i query with "FROM 2008-06-28, TO 2008-06-30" i get this result:

    Facility (1, 2)

    But I expected only 2.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you show your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2008
    Posts
    9
    Code:
    SELECT *
    FROM Facilities
    INNER JOIN Reservations ON Facilities.id = facility_id
    WHERE reservation_to <= '2008-06-28 14:00:00'
    OR reservation_from >= '2008-06-30 12:00:00'
    Tables are still:

    Code:
    TABLE `Facilities` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(150) NOT NULL,
      `price` double NOT NULL,
      `description` text NOT NULL,
      PRIMARY KEY  (`id`)
    )
    
    TABLE `Reservations` (
      `id` int(11) NOT NULL auto_increment,
      `customer_id` int(11) NOT NULL,
      `facility_id` int(11) NOT NULL,
      `reservation_from` datetime NOT NULL,
      `reservation_to` datetime NOT NULL,
      PRIMARY KEY  (`id`)
    )

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your query is incorrect

    change this --
    Code:
    WHERE reservation_to <= '2008-06-28 14:00:00'
    OR reservation_from >= '2008-06-30 12:00:00'
    to this --
    Code:
    WHERE reservation_to >= '2008-06-28 14:00:00'
    AND reservation_from <= '2008-06-30 12:00:00'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2008
    Posts
    9
    Ok, thank you.

    Now I get the occupied facilities on a given date.

    That is ok, but is there a clever way to get the available facilities or do I have to make another query on the facilities and loop through all facilities and only show the available ones like this:

    Query 1, on the given date facility 1 is occupied and the query return:
    1

    Query 2, return all facilities:
    1
    2

    Loop and remove even id's will return:
    2

    But are there better ways to do the same?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can do it in one query

    this query gives you all the available facilities in the requested time preiod:
    Code:
    SELECT f.id
         , f.name
         , f.price
         , f.description
      FROM Facilities AS f
    LEFT OUTER
      JOIN ( SELECT DISTINCT facility_id
               FROM Reservations 
              WHERE reservation_to   >= '2008-06-28 14:00:00' 
                AND reservation_from <= '2008-06-30 12:00:00'
           ) as r
        ON r.facility_id = f.id
     WHERE r.facility_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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