From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Lucas Adamski <ladamski(at)manageww(dot)com> |
Cc: | "Postgresql Performance Mailing list (E-mail)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Hack around lack of CORRESPONDING BY in EXCEPT? |
Date: | 2003-05-07 19:36:42 |
Message-ID: | 20030507123007.G29826-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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. :)
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Hatcher | 2003-05-07 19:40:19 | Re: Hack around lack of CORRESPONDING BY in EXCEPT? |
Previous Message | Lucas Adamski | 2003-05-07 19:11:46 | Hack around lack of CORRESPONDING BY in EXCEPT? |