Received: from lscorp.com (poohbah.lscorp.com [207.152.64.2]) by hub.org (8.8.8/8.7.5) with ESMTP id SAA27334 for ; Wed, 5 Aug 1998 18:49:01 -0400 (EDT) Received: from [207.152.64.133] by lscorp.com with SMTP (Eudora Internet Mail Server 1.2); Wed, 5 Aug 1998 17:47:21 -0500 X-Sender: lynch@poohbah.lscorp.com Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" To: Thomas Good From: lynch@lscorp.com (Richard Lynch) Subject: Re: mere millimetres away Cc: PostgreSQL Lifeline Date: Wed, 5 Aug 1998 17:47:21 -0500 At 4:15 PM 8/5/98, Thomas Good wrote: >Rich, >--------------------------------------------------------- >I got the following to work fine (so I had to break it!): >--------------------------------------------------------- >SELECT tr_id, tr_date >FROM crtrd1 ALIAS1 >WHERE ALIAS1.tr_unit = 'SMA' > AND (ALIAS1.tr_type = 'A' OR ALIAS1.tr_type = 'I') > AND ALIAS1.tr_id NOT IN ( > SELECT tr_id > FROM crtrd1 ALIAS2 > WHERE ALIAS2.tr_unit = 'SMA' > AND ALIAS2.tr_id = ALIAS1.tr_id > AND (ALIAS2.tr_type = 'T' OR ALIAS2.tr_type = 'O') > AND ALIAS2.tr_date > ALIAS1.tr_date > ) >ORDER BY tr_date DESC; > >---------------------------------------------------------------------- >I need to include the patient names so I tried a join to another table >---------------------------------------------------------------------- > SELECT crtrd1.tr_id, tr_date, client_lname, client_fname > FROM crtrd1 ALIAS1, svcrd1 ALIAS0 > WHERE ALIAS1.tr_unit = 'SMA' > AND ALIAS1.tr_id = ALIAS0.tr_id > AND (ALIAS1.tr_type = 'A' OR ALIAS1.tr_type = 'I') > AND NOT EXISTS( > SELECT crtrd1.tr_id > FROM crtrd1 ALIAS2 > WHERE ALIAS2.tr_unit = 'SMA' > AND ALIAS2.tr_id = ALIAS1.tr_id > AND (ALIAS2.tr_type = 'T' OR ALIAS2.tr_type = 'O') > AND ALIAS2.tr_date > ALIAS1.tr_date > ) >ORDER BY tr_date DESC; You should try doing analyze on these things to see why they are taking so long... I don't see anything wrong with this one, but I'm *NOT* an SQL expert. It may be trying to create a table that's just too large... -- -- -- "TANSTAAFL" Rich lynch@lscorp.com