Received: from junk.nocrew.org (mail@[212.73.17.42])
	by hub.org (8.10.1/8.11.0) with ESMTP id e9QDrIU30556
	for <pgsql-sql@postgresql.org>; Thu, 26 Oct 2000 09:53:18 -0400 (EDT)
	(envelope-from tomas@junk.nocrew.org)
Received: from tomas by junk.nocrew.org with local (Exim 3.12 #1 (Debian))
	id 13onSv-0004Zl-00; Thu, 26 Oct 2000 15:53:09 +0200
To: josh@agliodbs.com
Cc: sqllist <pgsql-sql@postgresql.org>
Subject: Re: Query Problem
References: <39F78FE7.DA915769@agliodbs.com>
From: Tomas Berndtsson <tomas@nocrew.org>
Date: 26 Oct 2000 15:53:09 +0200
In-Reply-To: Josh Berkus's message of "Wed, 25 Oct 2000 18:59:03 -0700"
Message-ID: <80og07675m.fsf@junk.nocrew.org>
Lines: 48
User-Agent: Gnus/5.0803 (Gnus v5.8.3) Emacs/20.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200010/239
X-Sequence-Number: 1906

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