Re: Hotel Booking dates subquery

Scotty wrote:
>Jan Hidders wrote:
>
>>Ahem, :-), you need to swap the 17-11-02 and the 20-11-02, and, yes, I did
>>check if I read the '<' and '>' correctly. :-) Remember that the formula was:
>>
>> end1 < begin2 or begin1 > end2
>>
>>where one period is [begin1, end1] and the other period is [begin2, end2].
>
>It's all in vain Jan, Damjam has just put the mockers on my query, It
>needs something of an overhaul.

Of course. This was just the first step, i.e., writing a query that finds the bookings that do not overlap with the overlap. As Steve Kass already explained you now have to do the following:

1. invert the query, i.e., transform it into the query that finds all bookings that *do* overlap with the period (Hint: just put a NOT at the beginning of the WHERE clause)
2. use the result as a subquery to construct the final query that asks for all rooms for which there is no booking that overlaps with the period

Good luck,

• Jan Hidders