Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!news3.google.com!newshub.sdsu.edu!logbridge.uoregon.edu!hammer.uoregon.edu!News.Dal.Ca!ursa-nb00s0.nbnet.nb.ca!53ab2750!not-for-mail
From: Bob Badour <bbadour@pei.sympatico.ca>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.theory
Subject: Re: SQL for intervals
References: <1165015487.535910.110510@j44g2000cwa.googlegroups.com>   <UBgch.27575$cz.418051@ursa-nb00s0.nbnet.nb.ca> <1165907925.619089.264790@73g2000cwn.googlegroups.com>
In-Reply-To: <1165907925.619089.264790@73g2000cwn.googlegroups.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 48
Message-ID: <2Wyfh.31895$cz.476399@ursa-nb00s0.nbnet.nb.ca>
Date: Tue, 12 Dec 2006 14:21:50 GMT
NNTP-Posting-Host: 142.176.111.41
X-Complaints-To: abuse@aliant.net
X-Trace: ursa-nb00s0.nbnet.nb.ca 1165933310 142.176.111.41 (Tue, 12 Dec 2006 10:21:50 AST)
NNTP-Posting-Date: Tue, 12 Dec 2006 10:21:50 AST
Organization: Sympatico-Subscriber
Xref: usenetserver.com comp.databases.theory:160371
X-Received-Date: Tue, 12 Dec 2006 09:21:50 EST (text.usenetserver.com)

Tonkuma wrote:

> Bob Badour wrote:
> 
>>select id_lwr, min_t as lwr_bound, min(max_t) as upr_bound
>>from (
>>    select id as id_lwr, t as min_t from W W1 where not exists (
>>        select 1 from W W2 where W1.t = W2.t + 1 and W1.id = W2.id
>>    )
>>) WL,
>>(
>>    select id as id_upr, t as max_t from W W3 where not exists (
>>        select 1 from W W4 where W3.t = W4.t - 1 and W3.id = W4.id
>>    )
>>) WU
>>where id_lwr = id_upr and max_t > min_t
>>group by id_lwr, min_t
>>;
>>
>>Depending on your flavour of SQL, you might have to change the simple
>>additions to function calls like DATE_ADD etc.
> 
> Same kind of thinking. But it is not necessary to use nested table
> expression.
> SELECT lbound.id
>      , CHAR(lbound.t,USA)      "LowerBound"
>      , CHAR(MIN(ubound.t),USA) "UpperBound"
>   FROM W lbound
>      , W ubound
>  WHERE NOT EXISTS
>        (SELECT *
>           FROM W lower
>          WHERE lower.id = lbound.id
>            AND lower.t  = lbound.t - 1 day
>        )
>    AND ubound.id = lbound.id
>    AND ubound.t >= lbound.t
>    AND NOT EXISTS
>        (SELECT *
>           FROM W upper
>          WHERE upper.id = lbound.id
>            AND upper.t  = ubound.t + 1 day
>        )
>  GROUP BY
>        lbound.id, lbound.t

How is a table expression nested in the where clause any less nested 
than one in the from clause?