0

This question already has an answer here:

Imagine the structure of a simple hotel booking app:

hotel_chain(id, name)
hotel(id, hotel_chain_id, name)
room(id, hotel_id)
booking(room_id, date_start, date_end)

There are hotels, that belong to an organization (chain), every hotel has rooms, and you can book a room for a given amount of time (date range).

Now, I want to do a query to find which rooms are available in a specific range, for a specific hotel chain. Which would be the more efficient way of getting this information.

Right now I can come up with this query that has a left anti semi join:

select h.id, h.name, r.id
from hotel h
inner join room r on r.hotel_id = h.id
where h.hotel_chain_id = @hotel_chain_id
and not exists
(   
    select b.id 
    from booking b
    where b.room_id = r.id
    and @start <= b.date_end
    and @end >= b.date_start
)

Which is the appropriated way of indexing for this query? Or a better query that can be indexed for high performance?

marked as duplicate by Josh Darnell, Community Apr 4 at 16:38

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.