Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 80C42475ADF for ; Thu, 16 May 2002 17:54:25 -0400 (EDT) Received: from wildbrain.com (unknown [64.1.15.36]) by postgresql.org (Postfix) with ESMTP id 16EC1475AD1 for ; Thu, 16 May 2002 17:54:24 -0400 (EDT) Received: from wildbrain.com (spock.wildbrain.com [10.20.2.38]) by wildbrain.com (8.12.2/8.12.2) with ESMTP id g4GLsLTP023854; Thu, 16 May 2002 14:54:21 -0700 Message-ID: <3CE42A8D.7050305@wildbrain.com> Date: Thu, 16 May 2002 14:54:21 -0700 From: Gregory Brauer User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0rc2) Gecko/20020510 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joel Burton , pgsql-sql@postgresql.org Subject: Re: SQL over my head... References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200205/180 X-Sequence-Number: 7815 Joel Burton wrote: >>-----Original Message----- >>From: Gregory Brauer [mailto:greg@wildbrain.com] >>Sent: Thursday, May 16, 2002 4:39 PM >>To: Joel Burton >>Cc: pgsql-sql@postgresql.org >>Subject: Re: [SQL] SQL over my head... >> >>>SELECT F0.id >>> FROM Foo as F0 >>> JOIN Bar as B0 ON (F0.id=B0.id) >>> WHERE NOT EXISTS ( >>> (SELECT * >>> FROM Foo AS F1, >>> Bar as B1 >>> WHERE ts < CURRENT_TIME >>> AND F0.attr_a=F1.attr_a >>> AND F0.attr_b=F1.attr_b >>> AND B0.attr_a=B1.attr_a >>> AND B1.ts > B0.ts) Excellent! This was very close, and put me on the right track. Here is what ended up working: SELECT F0.id FROM Foo as F0 JOIN Bar as B0 ON (F0.bar_id=B0.id) WHERE NOT EXISTS (SELECT * FROM Foo AS F1, JOIN Bar as B1 ON (F1.bar_id=B1.id) WHERE B1.ts < CURRENT_TIMESTAMP AND F0.attr_a=F1.attr_a AND F0.attr_b=F1.attr_b AND B0.attr_a=B1.attr_a AND B1.ts > B0.ts); There was an extra "(", and it should be CURRENT_TIMESTAMP. Thanks! Greg