www.webdeveloper.com
Results 1 to 6 of 6

Thread: [MySQL 5] Fetching records for common dates

  1. #1
    Join Date
    Sep 2006
    Posts
    655

    Question [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.



    The following is the order table:






    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.






    Please if anyone can help?

    Many thanks in advance

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,222
    Maybe something like this?
    Code:
    SELECT date_booked, COUNT(*) AS the_count GROUP BY date_booked HAVING the_count = 8
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Sep 2006
    Posts
    655
    Hi

    Thanks for your reply.

    Since the number of rooms can vary, may be 10,20,30 etc, so do I need to fire a different query and find out the count and store it in a variable and then run your query?

    Like this?
    Code:
    ...HAVING the_count = $count

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,222
    Sounds reasonable to me.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Sep 2006
    Posts
    655
    THanks man

  6. #6
    Join Date
    Sep 2006
    Posts
    655
    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".


    Here is the table DDL

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





    Any help is appreciated.

    Thanks
    Last edited by phantom007; 12-21-2010 at 11:35 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 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
  •  
HTML5 Development Center



Recent Articles