Re: Hack around lack of CORRESPONDING BY in EXCEPT?

Stephan,

Bingo! That worked perfectly, thank you! I was considering something like
that, but couldn't figure out the syntax offhand to join two events tables
in that fashion. Didn't realize you could alias a table as well! Thanks
again,
Lucas.

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)com]
> Sent: Wednesday, May 07, 2003 3:59 PM
> To: Lucas Adamski
> Cc: Postgresql Performance Mailing list (E-mail)
> Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT?
>
>
>
> On Wed, 7 May 2003, Lucas Adamski wrote:
>
> > I wrote it originally as:
> >
> > 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)
> >
> > because each of these subqueries restricts the dataset
> greatly before doing
> > the join. I've simplified the actual problem (as the real
> code has a bunch
> > of extraneous stuff that makes it even more obtuse), but
> essentially, the
> > tracking table maintains a record of the last record type
> that was entered.
> > The type is incremented for each batch of events that is
> loaded. In this
> > case, I'm assuming that the latest batch is type=10 (or
> 5000, or 100000),
> > and the tracking table references a small subset of previous events
> > (possibly of types 1-9 in this example). This particular
> query is supposed
> > to return all tracking.pk's that are present in the
> previous batches (types)
> > but not in the latest batch (10). I didn't mean to make it
> quite so obtuse,
> > sorry. :)
>
> Maybe something like nominally like (quickly done so possibly wrong
> again):
>
> select tracking.pk, events.data1, events.data2 from
> tracking,events where not exists (select * from events e where
> e.type=10 and e.data1=events.data1 and e.data2=events.data2)
> and tracking.event_fk=event.pk
>
> Get all tracking/event combinations, not including those
> where the data1/2
> matches that of an event with type 10.
>
> That might give dups if there are multiple events rows with
> that pk for
> different types (but not 10).
>
>