-4

i have a question i would like to share with you..

Say, we have a site with hotel reservations and we have two fields DateIn and DateOut.

Well, i would like to see if all dates between the DateIn and DateOut are free...

I want to do it using mysql..

Like that:

SELECT hotelname FROM hotel WHERE ..

(all dates >= DateIn and dates<=DateOut are free) --> how can i say that in mysql?

Thank you in advance!! I think it is not something difficult but i can't find it...

goku toriyama
  • 37
  • 2
  • 6

1 Answers1

0

if field free is a bool (tinyInt), you could try something like this:

SELECT * FROM (
SELECT `hotelName`, MIN(`available`) AS _available FROM `hotels` WHERE dbdate BETWEEN 20130507 AND 20130511 GROUP BY `hotelName`)a
WHERE _available;

The above query gave both hotels from the test as a result, when you change 20130507 to 20130503, it will only return the Amsterdam Hotel.

with this as a test table:

CREATE TABLE `hotels` (
  `hotelName` varchar(20) DEFAULT NULL,
  `dbdate` int(11) DEFAULT NULL,
  `available` tinyint(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `hotels` */

insert  into `hotels`(`hotelName`,`dbdate`,`available`) values ('Amsterdam Hotel',20130503,1),('Amsterdam Hotel',20130504,1),('Amsterdam Hotel',20130505,1),('Amsterdam Hotel',20130506,1),('Amsterdam Hotel',20130507,1),('Amsterdam Hotel',20130508,1),('Amsterdam Hotel',20130509,1),('Amsterdam Hotel',201305010,1),('Amsterdam Hotel',201305011,1),('Amsterdam Hotel',201305012,1),('Utrecht Hotel',20130503,0),('Utrecht Hotel',20130504,0),('Utrecht Hotel',20130505,0),('Utrecht Hotel',20130506,0),('Utrecht Hotel',20130507,1),('Utrecht Hotel',20130508,1),('Utrecht Hotel',20130509,1),('Utrecht Hotel',201305010,1),('Utrecht Hotel',201305011,1),('Utrecht Hotel',201305012,0);
user369122
  • 792
  • 3
  • 13
  • 33