[MySQL 5] Fetching records for common dates
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
Maybe something like this?
SELECT date_booked, COUNT(*) AS the_count GROUP BY date_booked HAVING the_count = 8
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?
...HAVING the_count = $count
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
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.
Last edited by phantom007; 12-21-2010 at 11:35 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)