On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster