Re: Hack around lack of CORRESPONDING BY in EXCEPT?

On Wed, 7 May 2003, Lucas Adamski wrote:

> I'm not sure if this a performance question or a sql question really, but
> since my primarily peeve here is performance, here goes:
>
> I'm trying to write a query which takes the output of a join and shows me
> only what the items that are in the main join but not in the subselect of
> just one of the tables in the join, using EXCEPT.
>
> This is a little complicated, so please bear with me.
>
> 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)

Maybe something like (if I'm right in assuming that you want any event
whose data1 and data2 match an event having type 10):

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;


> The official solution to this I believe would be to just use CORRESPONDING
> BY, but that's not supported by PG (why exactly, oh why!)

Because it's not entry level SQL92 and noone's implemented it yet. :)