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
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)
UPDATE2
SELECT `hotelid`
FROM table1
WHERE `date` BETWEEN '2013-04-01' AND '2013-04-03'
AND quantity >=3
GROUP BY `hotelid`
HAVING COUNT(*) = DATEDIFF('2013-04-03', '2013-04-01') + 1
3 in a WHERE clause is a number of rooms that should be available.
Output
| HOTELID |
-----------
| 10 |
| 11 |