Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2008
    Posts
    36

    Question Unanswered: [MySQL 5] Fetching records for common dates

    Hi peps,

    I have a query about a hotel's room booking system.

    Please take a look into the room table.

    http://img152.imageshack.us/img152/5...ection001j.png

    The following is the order table:

    http://img69.imageshack.us/img69/8384/selection002m.png




    What query would fetch me the date(s) which has all rooms (8 in this case) booked on that date?

    For this case it would be 2010-12-10 and 2010-12-20, since all 8 rooms have been booked for the date.

    http://img253.imageshack.us/img253/5...ection003l.png




    Please if anyone can help?

    Many thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for homework, you have show us that you have made an effort yourself

    hint: GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Posts
    36

    Red face

    Code:
    select date_booked from orders group by date_booked
    ???

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cancer10 View Post
    Code:
    select date_booked from orders group by date_booked
    ???
    nice try

    what did you get when you tested it?

    never mind, i'll tell you -- you will get each distinct date where at least one room is booked

    that's not what you wanted, is it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2008
    Posts
    36
    Exactly so I am still looking for the right query

  6. #6
    Join Date
    Mar 2008
    Posts
    36
    Hi Again,

    I was wondering if that same thing is possible if I have two columns in my table

    "date_from" and "date_to" instead of just one column "date_booked" .


    So, is there anyway we can get the dates of all the 8 rooms that are booked for the dates between "date_from" and "date_to".


    Any help is appreciated.

    Thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure it's possible

    could you do a SHOW CREATE TABLE please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2008
    Posts
    36
    Here it is

    Code:
    CREATE TABLE `orders` (
      `orderID` int(10) NOT NULL AUTO_INCREMENT,
      `user_id` int(10) DEFAULT NULL,
      `room_id` int(10) DEFAULT NULL,
      `date_from` date DEFAULT NULL,
      `date_to` date DEFAULT NULL,
      PRIMARY KEY (`orderID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COMMENT=''
    Many thanks

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that works nicely

    what was your question again? find which 8 rooms?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2008
    Posts
    36
    Well, I would like to find all dates from columns "date_from" and "date_to" that has all the 8 rooms (http://img152.imageshack.us/img152/5...ection001j.png) booked.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i get it

    boy, as homework assignments go, this one's a doozie... when's it due?

    okay, what you'll need is a table of dates

    using an INNER JOIN, join the dates table to the bookings table, and in the ON clause, use BETWEEN

    then use GROUP BY and COUNT(*) and bob's your uncle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2008
    Posts
    36
    Hi

    But there is no dates table in this case.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so create one

    after all, you don't want to write a loop, do you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2008
    Posts
    36
    Great

    Thanks

  15. #15
    Join Date
    Mar 2008
    Posts
    36
    Hi again!

    I have a new requirement now.

    Apart from the orders table, I have another table (special_dates) from where the startdate and end date has to be fetch and considered those dates as to be booked,

    The DDL for special_dates:

    Code:
    CREATE TABLE `special_dates` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `title` VARCHAR(100) DEFAULT NULL,
       `start_date` DATE DEFAULT NULL,
      `end_date` DATE DEFAULT NULL,
     `rooms_to_book` INT(5) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MYISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
    EDIT: The dates entered in this table shall also be considered as BOOKED dates for the number of cottages specified in the "rooms_to_book" column.

    To understand more on what I am looking for, here is what I have modified your query to get the result but apparently its not working.


    Code:
    SELECT A.theDate, COUNT(*) AS reservations
    FROM allDates AS A, orders AS O, special_dates as S
    WHERE ( (A.theDate BETWEEN O.date_from AND O.date_to) OR (A.theDate BETWEEN P.start_date AND P.end_date))
    AND A.theDate BETWEEN '2010-12-1' AND '2010-12-31'
    GROUP BY A.theDate
    HAVING COUNT(*) = 8
    ORDER BY A.theDate

    It returns me rows with insane reservations counts.

    Could you please help me again?


    Many thanks

    PS: The allDates table contains all dates between 2010-12-01 to 2020-12-31
    Last edited by cancer10; 02-03-11 at 10:48.

Posting Permissions

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