[MySQL 5] Fetching records for common dates

[cancer10]

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

[NogDog]

Maybe something like this?

SELECT date_booked, COUNT(*) AS the_count GROUP BY date_booked HAVING the_count = 8

[cancer10]

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?

...HAVING the_count = $count

[NogDog]

Sounds reasonable to me.

[cancer10]

THanks man

[cancer10]

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

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