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])