Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Unanswered: mysql query which needs to do calculations

    I'm not so great with sql but I think what I want should be possible, I'm just not sure how to do it.

    I have a site which allows users to list their youth hostel. Potential customers can then browse these hostels and place bookings.

    Tables: (not including id's and other cols!)
    hostels
    -------
    max_guests

    bookings
    -------
    hostel_id
    check_in
    check_out
    guests

    When users search the site I allow them to select a checkin date, a checkout date, a place name, and the number of guests. Note that each hostel has a maximum number of guests which they can accommodate, for example 20.

    So say previously a booking has been made at a particular hostel for 8 people during the dates selected for the search, then I need to know that the maximum number of guests that particular hostel can accommodate between those dates is now 12, and it would only appear in searches for <= 12 guests.

    Likewise if that hostel had a couple more bookings and was at it's maximum capacity, then it shouldn't turn up in the search at all.

    At the moment I have a query which gets all the hostels but if there's any bookings for a hostel between the selected dates, then it's excluded from the results. I'd like to be able to get all the bookings for each hostel during the selected dates, count the numbers of guests booked in, and if that number subtracted from the max_guests of the hostel gives a result which is lower than the number of guests specified in the search, then exclude it from the results.

    at the moment my query looks like:
    SELECT DISTINCT `Hostel`.`id`, `Hostel`.*, `Hostel`.`id`
    FROM `hostelsdatabase`.`hostels` AS `Hostel`
    LEFT JOIN `hostelsdatabase`.`bookings` AS `bookings` ON (`bookings`.`hostel_id` = `Hostel`.`id`)
    LEFT JOIN `hostelsdatabase`.`users` AS `User` ON (`Hostel`.`user_id` = `User`.`id`)
    WHERE ((`Hostel`.`address_one` LIKE '%london%') OR (`Hostel`.`address_two` LIKE '%london%') OR (`Hostel`.`city` LIKE '%london%'))
    AND `Hostel`.`maxguests` >= 4
    AND NOT EXISTS (SELECT * FROM bookings WHERE `bookings`.`hostel_id` = `Hostel`.`id` AND NOT('2013-07-31' > `bookings`.`checkout` OR '2013-08-02' < `bookings`.`checkin`))
    LIMIT 10

    Can anyone tell me how I could modify that query to make it do what I need?

  2. #2
    Join Date
    Mar 2011
    Posts
    3
    Hey there,

    In case it encourages anyone to help me out I've stuck together a sqlfiddle: SQL Fiddle

    and a better description of my problem might be:

    two tables: Hostels and Bookings.

    Hostels have a maximum number of guests which they can accommodate. Bookings are made between two dates and have a column 'guests' for the number of guests in the booking.

    Users should be able to search for a hostel given an area, a checkin date, a checkout date and the number of guests in the party.

    My query at the moment finds all the hostels which don't have a booking between the dates but what I actually want is:

    The query to return all hostels where, if the hostel has bookings during the user specified period, then the sum of the guests involved in those bookings, subtracted from hostel.maxguests shows that there is enough space left in the hostel. IE the number needs to be more than the user specified guests number from the search.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try something like this.

    Not tested on MySQL.
    (Tested on DB2 9.7 on Windows.)

    It might be neccesary to amend to conform to MySQL syntax.
    (For example, you might want to change LATERAL, CROSS JOIN, FROM sysibm.sysdummy1, so on...)

    Code:
    SELECT h.*
     FROM  hostels AS h
     CROSS JOIN
           users   AS u
     WHERE
       (   h.address_one LIKE '%' || u.area || '%'
        OR h.address_two LIKE '%' || u.area || '%'
        OR h.city        LIKE '%' || u.area || '%'
       )
       AND NOT EXISTS
           (SELECT b1.check_in
             FROM  LATERAL
                   (SELECT DISTINCT
                           b.check_in
                     FROM  bookings AS b
                     WHERE b.hostel_id = h.id
                       AND b.check_in  <= u.check_out
                       AND b.check_out >= u.check_in
                   ) AS b1
             CROSS JOIN
                   LATERAL
                   (SELECT u.guests , h.id , h.max_guests FROM sysibm.sysdummy1
                   ) AS p(guests , id , max_guests)
             INNER JOIN
                   bookings AS b2
              ON   b2.hostel_id =  h.id
               AND b2.check_in  <= u.check_out
               AND b2.check_out >= u.check_in
               AND b1.check_in  BETWEEN b2.check_in
                                    AND b2.check_out
             GROUP BY
                   b1.check_in
             HAVING
                   SUM(b2.guests) + MAX(p.guests) > MAX(p.max_guests)
           )
    ;
    Last edited by tonkuma; 08-06-13 at 14:47. Reason: Replace WHERE condition in subquery b1. Replace table name "user" to "users".

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The query looks like to conform to SQL standard.

    Mimer SQL Developers - Mimer SQL-2003 Validator
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT h.*
     FROM  hostels AS h
     CROSS JOIN
           users   AS u
     WHERE
       (
           h.address_one LIKE '%' || u.area || '%'
        OR h.address_two LIKE '%' || u.area || '%'
        OR h.city        LIKE '%' || u.area || '%'
       )
       AND
           NOT EXISTS
           (SELECT b1.check_in
             FROM  LATERAL
                   (SELECT DISTINCT
                           b.check_in
                     FROM  bookings AS b
                     WHERE b.hostel_id = h.id
                       AND b.check_in  <= u.check_out
                       AND b.check_out >= u.check_in
                   ) AS b1
             CROSS JOIN
                   LATERAL
                   (SELECT u.guests , h.id , h.max_guests FROM sysibm.sysdummy1
                   ) AS p(guests , id , max_guests)
             INNER JOIN
                   bookings AS b2
              ON   b2.hostel_id =  h.id
               AND b2.check_in  <= u.check_out
               AND b2.check_out >= u.check_in
               AND b1.check_in  BETWEEN b2.check_in
                                    AND b2.check_out
             GROUP BY
                   b1.check_in
             HAVING
                   SUM(b2.guests) + MAX(p.guests) > MAX(p.max_guests)
           )
    ;

    Code:
     
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    F401, "Extended joined table"
    F281, "LIKE enhancements"
    T491, "LATERAL derived table"
    T501, "Enhanced EXISTS predicate"

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I might thought things too complex.

    This may be enough.
    Code:
    WITH booking_objects AS (
    SELECT b.*
     FROM  bookings AS b
     INNER JOIN
           users    AS u
      ON   b.check_in  <= u.check_out
       AND b.check_out >= u.check_in
    )
    SELECT h.*
     FROM  hostels AS h
         , users   AS u
     WHERE
       (   h.address_one LIKE '%' || u.area || '%'
        OR h.address_two LIKE '%' || u.area || '%'
        OR h.city        LIKE '%' || u.area || '%'
       )
       AND NOT EXISTS
           (SELECT 0
             FROM  booking_objects AS b1
             INNER JOIN
                   booking_objects AS b2
              ON   b1.check_in BETWEEN b2.check_in
                                   AND b2.check_out
             WHERE b1.hostel_id = h.id
               AND b2.hostel_id = h.id
             GROUP BY
                   b1.check_in
             HAVING
                   SUM(b2.guests) / COUNT(DISTINCT b1.book_id) + u.guests > h.max_guests
           )
    ;

Posting Permissions

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