From: Tomas Berndtsson <tomas@nocrew.org>
To: josh@agliodbs.com
Cc: sqllist <pgsql-sql@postgresql.org>
Subject: Re: Query Problem
Date: 26 Oct 2000 15:53:09 +0200
Message-ID: <80og07675m.fsf@junk.nocrew.org> (raw)
References: <39F78FE7.DA915769@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> Folks:
>
> Here's the problem, in abstract: I need to select every record in table
> A that does not have a link in table B Join Table C where Table
> C.account = 11
>
> The relevant fields:
>
> Table_A
> CaseID
> Data
>
> Table_B
> GroupID
> CaseID
> Amount
>
> Table_C
> GroupID
> AccountID
>
> Thus, I need to select:
>
> SELECT Data FROM Table A
> WHERE CaseID NOT IN (
> SELECT CaseID FROM Table_B, Table_C
> WHERE Table_B.GroupID = TableC.GroupID
> AND TableC.AccountID = 11)
>
> The problem is, since Table_B and Table_C are large (10,000 records +)
> this exclusion query takes several *minutes* to run.
>
> I've fooled around with drectional joins, views, and temporary tables,
> but I can seem to find anything that works faster. Suggestions?
Without having tried, something like this might be faster:
SELECT Data FROM Table A
WHERE CaseID NOT IN (
SELECT CaseID FROM Table_B
WHERE Table_B.GroupID IN (
SELECT GroupID FROM Table_C
WHERE TableC.AccountID = 11))
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tomas@nocrew.org, josh@agliodbs.com
Subject: Re: Query Problem
In-Reply-To: <80og07675m.fsf@junk.nocrew.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox