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]>
Subject: Re: [PERFORM] rewrite in to exists?


> >SELECT t1.code,, 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 

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