On Mon, Dec 02, 2002 at 06:10:39PM +0000, Malcolm Hutty wrote:
> Joel Burton wrote:
>
> >We can find these with:
> >
> >select id,
> > arrive
> >from trains t1
> >where t1.arrive + '7 hours' < all ( select depart
> > from trains t2
> > where t2.depart > t1.arrive );
>
> Thanks, that really helped. It was the "all" that did it; I'd been
> messing with IN and EXISTS and generally making a mess of it.
Glad to help.
Re: EXISTS, I think that this would be equivalent:
select id,
arrive
from trains t1
where not exists ( select *
from trains t2
where t2.depart > t1.arrive
and t2.depart - t1.arrive <= '7 hours' )
the t2.depart > t1.arrive is to get rid of most matches, rather than
relying on the mucher slower subtraction.
This might perform faster or slower than the the < ALL, depending on
your data, indexes, etc. I'd think it would be slower, but benchmark if
it's important. I think the first is definitely clearer, though.
BTW, for SQL novices, there's also ANY, similar to ALL, which finds
cases where there's any match. This can be easily switched for EXISTS.
If you'd like some help using these, I'd highly recommend Joe Celko's
_SQL_For_Smarties_.
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant