Room search sql query

Question

This is a sample program for a hotel agent where he gets contracts from various hotels and allows customers to search and book hotels that he has contracts with (and thus gets some profit). Here are my table schemas.

CONTRACT (contract_id, hotel_id, valid_from, valid_to) 
ROOM (room_type, hotel_id, contract_id, price, max_adults_allowed, avail_rooms)
HOTEL (hotel_id, hotel_name, location)

For searching, the interface like in hotels.com; i.e., for ex., a customer can specify it like '1 room with 2 adults' and '1 room 2 adults and 1 room 3 adults'.

Here is my sql query so far,

select h.name, h.location, r.type, r.price FROM HOTEL h, ROOM r, CONTRACT c 
WHERE c.contract_id = r.contract_id and c.hotel_id = h.hotel_id    --table joining
AND 'requested room lies within the contract valid period'  --check in and check out dates comparison
AND 'h.location is the user specified location' --requested location 
........

I have trouble integrating the room search details (as aforementioned) into this query. Can someone help me in writing the sql query to include room specifications. I'm using SQL Server. Thanks in advance.

Answer 1

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_id
AND 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)

Answer 2

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.#RoomParams
SELECT SUM(rooms) AS rooms, adults
INTO dbo.#RoomParams
FROM (VALUES(1, 2), -- 1 room with 2 adults
            (1, 2), -- 1 room with 2 adults
            (1, 3)) -- 1 room with 3 adults
p(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.#RoomParams
SELECT rooms, adults
INTO dbo.#RoomParams
FROM (VALUES(1, 2), -- 1 room with 2 adults
            (1, 2), -- 1 room with 2 adults
            (1, 3)) -- 1 room with 3 adults
p(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
                )