Re: Hack around lack of CORRESPONDING BY in EXCEPT?

On Wed, 7 May 2003 12:11:46 -0700, "Lucas Adamski"
<ladamski(at)manageww(dot)com> wrote:
>I have two tables: an event table that logs random events as they come in,
>and a tracking table that keeps a state of events it cares about. In this
>particular case I'm trying to obtain a list of tracking pkeys for related
>event data that do not correspond to a certain (other) set of event data.
>
>Ideally, here is what I want:
>
>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)

Lucas, try this untested query:

SELECT tr.pk, ev.data1, ev.data2
FROM tracking tr INNER JOIN events ev
ON tr.event_fk = ev.pk
WHERE ev.type != 10;

(Should also work with AND instead of WHERE.)

>SELECT tracking.pk,events.data1,events.data2 FROM tracking,events WHERE
>tracking.event_fk = event.pk EXCEPT (SELECT
>tracking.pk,events.data1,events.data2 FROM tracking,events WHERE
>tracking.event_fk = event.pk AND event.type = 10)
>
>That won't work for two reasons... first, there are no matching entries in
>the tracking table pointing to events where event.type = 10, meaning this
>query would always return an empty set.

I don't understand this. If there are no entries with event.type 10,
then the subselect returns an empty result set, and <anything> EXCEPT
<empty> should give the original result?

Servus
Manfred