mysql - How to retrieve data from database table with condition in between date? -

i need check available hotels , quantity of rooms available on hotels duration(start_date , end_date.

i have table test(id, date, qty, hotel_id). need access the hotel ids condition on qty (eg. qty>2) , date between 2 dates(for eg: date field of test table should have date value greater '2013-05-06' , end date less '2013-05-10').

i tried query:

select hotel_id   test  qty>2    , date between '2013-05-06' , '2013-05-10'; 

but, condition there must date between given dates. i.e. date field should have date date values: '2013-05-06', '2013-05-07', '2013-05-08', '2013-05-09', '2013-05-10' . if of above date missing, should return empty resultset. , if date available qty>2(let), should return list of hotel_ids.

how can done in single mysql query?

try

select `hotel_id`   test  `date` between '2013-05-06' , '2013-05-10'    , `qty` >= 2  group `hotel_id`  having count(*) = (datediff('2013-05-10', '2013-05-06') + 1) 

sqlfiddle