Re: Hack around lack of CORRESPONDING BY in EXCEPT?

Manfred,

I think what you propose is similar to what Patrick proposed, let me see if
I can explain below:

> -----Original Message-----
> From: Manfred Koizar [mailto:mkoi-pg(at)aon(dot)at]
> Sent: Wednesday, May 07, 2003 1:23 PM
> To: Lucas Adamski
> Cc: Postgresql Performance Mailing list (E-mail)
> Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT?
>

<snip>

> 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.)

The problem is that it simply removes all events where type != 10, versus
subtracting all events from subselect of type 10 where data1 and data2 match
those in the main join. The goal of the query is to remove all events that
match (matching being defined as both data1 and data2 matching) that are
present in events of type 10 and events that are referenced by the tracking
table, then return those tracking.pk's for entries that are left over.

Its not required that I join tracking and events in the primary select
before doing the EXCEPT join, but it should make it a bit more efficient.

>
> >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?

Its not that there are no entires with event.type=10, its that there may not
be any tracking entires for events of type 10, and if I join them before
doing the EXCEPT I will lose them. That's why I have to do the EXCEPT
subselect without joining it to the table. Thanks,
Lucas.

>
> Servus
> Manfred
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>