Re: Hotel Booking dates subquery

"Jan Hidders" <hidders_at_REMOVE.THIS.uia.ua.ac.be> wrote in message news:3dd02357$1_at_news.uia.ac.be...
> 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

... and you'll get exactly the same query I posted. Actually there are two approaches, you can use De Morgan's law and negate the condition in your query, or you can write the opposite condition from the scratch (look back at the post with schematical diagram of all possible situations). Also, check Mr. Jan's hint about using "NOT IN" couple posts ago!

Best wishes,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/ Received on Tue Nov 12 2002 - 08:30:53 CET