0

I need to check the available hotels and quantity of rooms available on the hotels for some duration(start_date and end_date.

I have a table test(id, date, qty, hotel_id). I need to access all the the hotel ids with some condition on qty (eg. qty>2) and date between two dates(for eg: the date field of the test table should have date value greater than the '2013-05-06' and end date less than '2013-05-10').

I tried this query: 

select hotel_id
  from test
 where qty>2
   and date between '2013-05-06' and '2013-05-10';

But, another condition is there must be all date between given dates. i.e. the date field should have all date date values: '2013-05-06', '2013-05-07', '2013-05-08', '2013-05-09', '2013-05-10' . If any of the above date is missing, then it should return empty resultset. And if all date are available with qty>2(let), then it should return list of hotel_ids.

How can it be done in a single MySQL query?

Sandesh Sharma
  • 1,064
  • 5
  • 16
  • 32
  • 2
    Possible duplicate http://stackoverflow.com/a/16393694/1920232. Just answered the same question. Is it a homework? – peterm May 06 '13 at 07:49
  • You are doing it correctly, what is the problem? If you are confused with the `between` in `where` clause have a look [`MySQL BETWEEN Clasue`](http://www.tutorialspoint.com/mysql/mysql-between-clause.htm) – Subedi Kishor May 06 '13 at 07:50
  • @KishorSubedi, no, i am not doing it correctly. suppose date doesn't contain '2013-05-07', but only contains '2013-05-06', '2013-05-08', '2013-05-09', '2013-05-10'. Then it should return empty result set. Because i need the resultset(hotel id) if there are all the date available between start date and end date. So, how to check that there are all the date in between start date and end date and return the hotel id with qty>2 in above example??? – Sandesh Sharma May 06 '13 at 10:35
  • @peterm no, it's not a homework. And i have gone through your link too, but that answer is not my required answer. Can u plz help me to solve it??? – Sandesh Sharma May 06 '13 at 10:37
  • see this [mysql-select-all-data-between-two-dates][1] [1]: http://stackoverflow.com/questions/1080207/mysql-select-all-data-between-two-dates – enigmaticus May 06 '13 at 12:29

1 Answers1

0

Try

SELECT `hotel_id`
  FROM test
 WHERE `date` BETWEEN '2013-05-06' AND '2013-05-10'
   AND `qty` >= 2
 GROUP BY `hotel_id`
 HAVING COUNT(*) = (DATEDIFF('2013-05-10', '2013-05-06') + 1)

SQLFiddle

peterm
  • 91,357
  • 15
  • 148
  • 157