Room search sql query
Assuming you've already parsed the "natural language" input, group your requests by max_adults, then query for each separate type; e.g. for '2 rooms 1 adult and 3 rooms 2 adults' issue two separate queries with (max_adults = 1, avail_rooms = 2) and (max_adults = 2, avail_rooms = 3)
select h.name, h.location, r.type, r.price, r.room_id FROM HOTEL h, ROOM r, CONTRACT c WHERE c.contract_id = r.contract_id and c.hotel_id = h.hotel_idAND c.valid_from >= ? AND c.valid_to <= ? AND h.location = ?AND r.max_adults = ? AND r.avail_rooms >= ?Note that this will only find exact matches for max_adults and will not give you the option to "underbook" a room. If you want to allow customers to book large rooms (and pay accordingly, without filling them up), you can use the following strategy: first try the original query, then search for larger rooms (by adding "artificial guests").
For example, if the original request was '1 triple, 1 double, 1 single', try that first, then '1 triple 2 doubles', then '2 triples 1 double', then '3 triples'. For each "modified" query use the SQL above. The first one that succeeds will be the least expensive option for the customer.
Even though this involves several SQL transactions, each one has simple joins and will probably work faster than a complex statement.
If you had more control you would probably change the schema to account for each room individually:
ROOM (room_id, room_type, hotel_id, contract_id, price, max_adults_allowed)BOOKING (room_id, checkin, checkout)
This script retrieve hotels which have only required rooms. If hotel have only one room from necessary then it is hotel excluded.
UPDATE 15.01.2013
IF OBJECT_ID('tempdb.dbo.#RoomParams') IS NOT NULL DROP TABLE dbo.#RoomParamsSELECT SUM(rooms) AS rooms, adultsINTO dbo.#RoomParamsFROM (VALUES(1, 2), -- 1 room with 2 adults (1, 2), -- 1 room with 2 adults (1, 3)) -- 1 room with 3 adultsp(rooms, adults)GROUP BY adults;WITH cte AS ( SELECT h.hotel_id, h.hotel_name, h.location, r.room_type, r.price, SUM(avail_rooms) OVER (PARTITION BY h.hotel_id, r.max_adults_allowed) AS cnt, r.max_adults_allowed FROM CONTRACT c JOIN ROOM r ON c.contract_id = r.contract_id AND c.hotel_id = r.hotel_id JOIN HOTEL h ON c.hotel_id = h.hotel_id WHERE c.valid_from >= '20130114' AND c.valid_to <= '20130115' --check in and check out dates comparison AND h.location IN ('loc4') --requested location ) SELECT * FROM cte s WHERE NOT EXISTS ( SELECT rp.adults FROM dbo.#RoomParams rp EXCEPT SELECT st.max_adults_allowed FROM cte st JOIN dbo.#RoomParams r ON st.cnt >= r.rooms AND st.max_adults_allowed = r.adults WHERE st.hotel_id = s.hotel_id ) AND s.max_adults_allowed IN (SELECT adults FROM dbo.#RoomParams)Demo on SQLFiddle
If you want retrieve all hotels which have at least one of options
IF OBJECT_ID('tempdb.dbo.#RoomParams') IS NOT NULL DROP TABLE dbo.#RoomParamsSELECT rooms, adultsINTO dbo.#RoomParamsFROM (VALUES(1, 2), -- 1 room with 2 adults (1, 2), -- 1 room with 2 adults (1, 3)) -- 1 room with 3 adultsp(rooms, adults);WITH cte AS ( SELECT h.hotel_id, h.hotel_name, h.location, r.room_type, r.price, SUM(avail_rooms) OVER (PARTITION BY h.hotel_id, r.max_adults_allowed) AS cnt, r.max_adults_allowed FROM CONTRACT c JOIN ROOM r ON c.contract_id = r.contract_id AND c.hotel_id = r.hotel_id JOIN HOTEL h ON c.hotel_id = h.hotel_id WHERE c.valid_from >= '20130114' AND c.valid_to <= '20130115' --check in and check out dates comparison AND h.location IN ('loc4') --requested location ) SELECT hotel_name, location, room_type, price, max_adults_allowed, cnt FROM cte c WHERE EXISTS ( SELECT 1 FROM dbo.#RoomParams r WHERE c.cnt >= r.rooms AND c.max_adults_allowed = r.adults )