var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: [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
for homework, you have show us that you have made an effort yourself
hint: GROUP BY
select date_booked from orders group by date_booked
Originally Posted by
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
Exactly so I am still looking for the right query
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.
sure it's possible
could you do a SHOW CREATE TABLE please
Here it is
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=''
that works nicely
what was your question again? find which 8 rooms?
Well, I would like to find all dates from columns "date_from" and "date_to" that has all the 8 rooms (
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
But there is no dates table in this case.
so create one
after all, you don't want to write a loop, do you?
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:
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.
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
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.
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?
PS: The allDates table contains all dates between 2010-12-01 to 2020-12-31
Last edited by cancer10; 02-03-11 at