Thanks Josh!

But, the EXISTS version doesn't really give me what I want...all rows in tbl that 
match the date of the subquery.

But, using the DISTINCT does make sense.

Thanks again to all who helped!

-----Original Message-----
From: Josh Berkus <[EMAIL PROTECTED]>
Sent: Sep 18, 2003 10:34 AM
To: LN Cisneros <[EMAIL PROTECTED]>, LN Cisneros <[EMAIL PROTECTED]>, 
        Manfred Koizar <[EMAIL PROTECTED]>, 
        Christopher Kings-Lynne <[EMAIL PROTECTED]>
Cc: LN Cisneros <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
Subject: Re: [PERFORM] rewrite in to exists?

Laurette,

> >SELECT t1.code, t1.id, t1.date_of_service
> > FROM tbl t1 INNER JOIN
> >       (SELECT DISTINCT date_of_service
> >         FROM tbl
> >         WHERE xxx >= '29800' AND xxx <= '29909'
> >          AND code = 'XX'
> >      ) AS t2 ON (t1.date_of_service = t2.date_of_service)
> > WHERE t1.client_code = 'XX'
> > ORDER BY id, date_of_service;
>
> A question I have is is the "DISTINCT" really going to help or is it just
> going to throw another sort into the mix making it slower?

It's required if you expect the subquery to return multiple rows for each 
date_of_service match.  Of course, you can also put the DISTINCT in the main 
query instead; it depends on how many results you expect the subquery to 
have.

Still, I'd suggest trying the EXISTS version first .... under most 
circumstances, DISTINCT is pretty slow.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to