Re: Hack around lack of CORRESPONDING BY in EXCEPT?

Stephan,

Yup, unfortunately you are correct... I'd need to get the event.pk's out of
there somewhere to join with the tracking.event_fk. I can't put the
event.pk in the subselects as they don't match, and I would get an empty set
back.

select tracking.pk, e.data1, e.data2 from
tracking,
((select data1,data2 from events) except (select data1,data2 from events
where event.type=10)) e
where tracking.event_fk=e.pk;

I wrote it originally as:

SELECT tracking.pk,events.data1,events.data2 FROM tracking,events WHERE
tracking.event_fk = event.pk EXCEPT (SELECT events.data1,events.data2 FROM
events WHERE event.type = 10)

because each of these subqueries restricts the dataset greatly before doing
the join. I've simplified the actual problem (as the real code has a bunch
of extraneous stuff that makes it even more obtuse), but essentially, the
tracking table maintains a record of the last record type that was entered.
The type is incremented for each batch of events that is loaded. In this
case, I'm assuming that the latest batch is type=10 (or 5000, or 100000),
and the tracking table references a small subset of previous events
(possibly of types 1-9 in this example). This particular query is supposed
to return all tracking.pk's that are present in the previous batches (types)
but not in the latest batch (10). I didn't mean to make it quite so obtuse,
sorry. :)

So in this case I'm getting all of the relevant data for the new entries,
subtracting those from the old entries that are referred to by the tracking
system, and returning those outdated tracking.pk's.
Lucas.

-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)com]
Sent: Wednesday, May 07, 2003 12:43 PM
To: Lucas Adamski
Cc: Postgresql Performance Mailing list (E-mail)
Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT?

On Wed, 7 May 2003, Lucas Adamski wrote:

Of course my last suggestion won't work since you need to get the event.pk
field out. The actual subquery would need to be more complicated and
probably involve an IN or EXISTS. :(