On Wed, 7 May 2003 12:11:46 -0700, "Lucas Adamski"
<ladamski@manageww.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
