Re: Unique Keys

Mikito Harakiri wrote:
> "strider5" <strider5_at_szm.com> wrote in message
news:<1101405872.089624.49260_at_z14g2000cwz.googlegroups.com>...
> > CREATE TABLE reservations (
> > hotel_room char(4),
> > span DATEINTERVAL )
> >
> > select 0 from reservations r1 NATURAL JOIN reservations r2
> > where OVERLAPS( r2.SPAN , r1.SPAN) = {}
>
> NATURAL JOIN syntax is so goofy! Isn't your view equivalent to
>
> select 0 from reservations r1, reservations r2
> where r1.hotel_room = r2.hotel_room
> AND r1.SPAN = r2.SPAN
> ^^^^^^^^^^^^^^^^^^^^^
> and OVERLAPS( r2.SPAN , r1.SPAN)
>
> which always evaluates to empty set?

Goofy is right. To use NATURAL JOIN that query would have to be re-written as:

select 0 from
(select hotel_room, span as r1_span from reservations) r1 NATURAL JOIN
(select hotel_room, span as r2_span from reservations) r2 where OVERLAPS( r2_SPAN , r1_SPAN) = {}
... which rather loses the intended elegance, doesn't it? Received on Fri Nov 26 2004 - 14:19:01 CET