> > SELECT room_no
> >   FROM rooms
> >  WHERE (date_in <= S AND date_out > S)
> >     OR (date_in > S  AND date_in < E)
>
> May I suggest something simpler?
>
> WHERE (date_in < E and date_out > S)
>
> I _think_ that's all you need.


Yeah -- you're totally right.

The funny thing is that I was just writing this really long explanation
about further simplifying the WHERE clause using truth tables and boolean
algebra, the the expression really can't be simplified any more, not without
taking in the "real world" as a consideration.


The short and sweet (for those who care) is this:


A is  check in  ON OR BEFORE S   (date_in <= S)
B is  check out AFTER S          (date out > S)
C is  check in  BEFORE E         (date_in < E )

Therefore, !A is  date_in > S


My where clause,

WHERE (date_in <= S AND date_out > S)
   OR (date_in > S  AND date_in < E)

is represented like this in a boolean expression

(A & B) | (!A & C)


I can't attach images to these posts, so I can't draw the Venn diagram, but
you see in the diagram that the expression itself can't be simplified
further.  Each sub expression specifies a disjoint set from the other.

  http://www.venndiagram.com/vennblank.gif


This is where the "real world" comes into play.

We realize that there exist the following impossibilities:

It's impossible to have A be true if C is false
     -- We can't check in BEFORE S AND check in AFTER E.

It's impossible to have B be false when A is true
     -- We can't check out before we check in.

It's impossible to have B be false if C is false
     -- We can't check out before we check in.


This means that instead of a traditional looking 3-circle Venn diagram, we
look at essentially the MasterCard logo -- there are no possible values
outside of B | C, and the set space for A is completely contained within the
set spaces for B and C.

More specifically, we can see that the only valid valid values for A are at
the intersection of B and C.


Okay, that's enough rambling for now... back to my real job. =)


nik