Re: SQL over my head... - Mailing list pgsql-sql

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 *
FROMFoo 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