Re: Hack around lack of CORRESPONDING BY in EXCEPT?

Patrick,

I don't think that wouldn't quite work unfortunately, as I'm actually trying
to filter them out based upon the values in data1 and data2. I'm using the
data in set 2 (data1,data2 from events where type=10) to remove rows from
set 1 (join between events and tracking table) where set1.data1=set2.data1
and set1.data2=set2.data2, and returning the tracking id's for any rows left
in set 1 (that were not in set 2). I probably gave a better explaination in
my response to Stephan. In the case below, I would simply get all events
where type<>10 from the join, regardless of whether they matched the data1
and data2 for all type=10. Thanks,
Lucas.

-----Original Message-----
From: Patrick Hatcher [mailto:PHatcher(at)macys(dot)com]
Sent: Wednesday, May 07, 2003 12:40 PM
To: ladamski(at)manageww(dot)com
Cc: Postgresql Performance Mailing list (E-mail);
pgsql-performance-owner(at)postgresql(dot)org
Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT?

Not sure if I'm reading your question correctly, but is this what you want?

SELECT t.pk,e.data1,e.data2
FROM tracking t left outer join events e on t.event_fk = e.pk
WHERE e.type <> 10

OR

SELECT t.pk,e.data1,e.data2
FROM tracking t inner join events e on t.event_fk = e.pk
WHERE e.type <> 10

"Lucas Adamski"
<ladamski(at)manageww(dot)com> To: "Postgresql
Performance Mailing list (E-mail)"
Sent by:
<pgsql-performance(at)postgresql(dot)org>
pgsql-performance-owner(at)post cc:
gresql.org Subject:
[PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT?

05/07/2003 12:11 PM

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)

The problem I have of course is that I get an error regarding trying to use
different columns for the two queries in EXCEPT. I'm sure someone will
point this out, but the following suggestion will not work:

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. And even if there were, I don't
want to do the join twice if its not necessary, as the events table is
liable to be very large.

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

Suggestions, anyone? Thanks in advance,
Lucas.