检索用户预订系统的唯一酒店ID

Question

I have a sql related problem. I need to write a complex query for the table described below

id  date        quantity    roomtypeid  hotelid

User will search hotels using check in and check out date and specify numbers of room(quantity) with the search query, so what I need to do is get hotel ids from above table which have minimum number of rooms available from check in to check out date.

1   04-01-2013  3   4   10
2   04-02-2013  3   4   10
3   04-03-2013  3   4   10
4   04-04-2013  3   4   10
5   04-01-2013  3   4   11
6   04-02-2013  3   4   11
7   04-03-2013  3   4   11
8   04-04-2013  3   4   11
9   04-01-2013  3   4   12
10  04-02-2013  3   4   12

The resulting query should only give 10,11 as hotel id if user wants to book from 04-01-2013 to 04-03-2013 with 3 rooms.

Regards Subash

Answer 1

SELECT DISTINCT(`hotelid`) FROM `tableName` WHERE `date` = $checkindate AND `date` = $checkoutdate AND `quantity` = $numberofroom

Use the above query. it is only dummy query modify it according you(fields name and variable names)