Re: rewrite in to exists?

On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne"
<chriskl(at)familyhealth(dot)com(dot)au> wrote:
>Why can't you just go:
>
>select code, id, name, date_of_service from tbl where xxx <= 29800 and xx >=
>29909 and code='XX' and client_code='XX' order by id, date_of_service;

Because (ignoring conditions on code and client_code for a moment) if
for a given date there is at least one row satisfying the condition on
xxx, the original query returns *all* rows having this date,
regardless of their xxx value. For example:

id | date | xxx
----+------------+-------
1 | 2003-01-01 | 10000 *
2 | 2003-01-01 | 29800 * *
3 | 2003-01-01 | 30000 *
4 | 2003-02-02 | 20000
5 | 2003-03-03 | 29900 * *

>> select code, id, name, date_of_service
>> from tbl
>> where date_of_service in
>> (select date_of_service
>> from tbl
>> where xxx >= '29800'
>> and xxx <= '29909'
>> and code = 'XX')
>> and client_code = 'XX'
>> order by id, date_of_service;

To the original poster: You did not provide a lot of information, but
the following suggestions might give you an idea ...

SELECT code, id, date_of_service
FROM tbl
WHERE EXISTS (SELECT *
FROM tbl t2
WHERE t2.xxx >= '29800' AND t2.xxx <= '29909'
AND t2.code = 'XX'
AND tbl.date_of_service = t2.date_of_service)
AND client_code = 'XX'
ORDER BY id, date_of_service;

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;

SELECT DISTINCT t1.code, t1.id, t1.date_of_service
FROM tbl AS t1 INNER JOIN tbl AS t2
ON (t1.date_of_service = t2.date_of_service
AND t2.xxx >= '29800' AND t2.xxx <= '29909'
AND t2.code = 'XX')
WHERE t1.client_code = 'XX' -- might as well put this
-- condition into the ON clause
ORDER BY id, date_of_service;

The last one assumes that there are no duplicates on code, id,
date_of_service in the desired result.

Servus
Manfred