[MySQL 5] Fetching records for common dates

cancer10 · #1 (**permalink**) 12-15-10, 12:51

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

r937 · #2 (**permalink**) 12-15-10, 13:12

for homework, you have show us that you have made an effort yourself

hint: GROUP BY

cancer10 · #3 (**permalink**) 12-15-10, 22:56

Code:

select date_booked from orders group by date_booked

???

r937 · #4 (**permalink**) 12-15-10, 23:08

Quote:

Originally Posted by cancer10

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

cancer10 · #5 (**permalink**) 12-16-10, 00:23

Exactly so I am still looking for the right query

cancer10 · #6 (**permalink**) 12-22-10, 00:25

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

r937 · #7 (**permalink**) 12-22-10, 00:27

sure it's possible

could you do a SHOW CREATE TABLE please

cancer10 · #8 (**permalink**) 12-22-10, 00:33

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

r937 · #9 (**permalink**) 12-22-10, 00:52

that works nicely

what was your question again? find which 8 rooms?

cancer10 · #10 (**permalink**) 12-22-10, 00:55

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.

r937 · #11 (**permalink**) 12-22-10, 01:22

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

cancer10 · #12 (**permalink**) 12-22-10, 01:33

Hi

But there is no dates table in this case.

r937 · #13 (**permalink**) 12-22-10, 01:45

so create one

after all, you don't want to write a loop, do you?

cancer10 · #14 (**permalink**) 12-22-10, 02:05

Great

Thanks

cancer10 · #15 (**permalink**) 02-03-11, 09:35

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

Internet Services

Web Development

Digital Marketing

Consumer Tech