Select question..... is there a way to do this?

Assume the following tables:

Table ITEM (user text, subject text, number integer, changed timestamp);
table SEEN (user text, number integer, lastviewed timestamp);

Ok, now the data in the "SEEN" table will have one tuple for each user
and number in the table ITEM which a user has viewed, and the last time
they looked at it.

It will also have one entry per user with a NULL number, which will be
written to the table when the user scans the table and finds no new
records (to keep the "SEEN" table from becoming exponentially large as
otherwise it would require USER X ITEMs tuples!); that record marks the
last time the user was there and there were no newer ITEMs.

If I do the following query:

select item.user, item.subject, item.number from item, seen where
(item.user = seen.user) and (item.number = seen.number) and
(item.changed > seen.lastviewed);

I get all items which have a record in SEEN.  So far so good.

But what I want is all items which EITHER have (1) a record in SEEN
which matches (and is older) OR which are NEWER than the SEEN record
with a NULL "number".

That I'm having trouble doing.

The following does not do what I want:

select item.user, item.subject, item.number from item, seen where
(item.user = seen.user and item.number = seen.number and item.changed >
seen.lastviewed) OR (item.user = seen.user and item.changed >
seen.lastviewed and seen.number is null);

That second query returns TWO entries for a SEEN record (which I can
control out with "Select distinct") BUT it breaks in another nasty way -
if I have an item that has a CHANGED time that is later than the null
record, *IT GETS SELECTED EVEN IF IT HAS A DISTINCT RECORD*.  That's no
good.

Ideas?

(Attempted to use "AND NOT" as a conditional on the second clause to the
OR and that didn't work; it excluded all of the NULL records....)

--
Karl Denninger (karl@denninger.net)
http://www.denninger.net




%SPAMBLOCK-SYS: Matched [@postgresql.org+], message ok