Message-ID: <39F78FE7.DA915769@agliodbs.com>
Date: Wed, 25 Oct 2000 18:59:03 -0700
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
MIME-Version: 1.0
To: sqllist <pgsql-sql@postgresql.org>
Subject: Query Problem
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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?

-Josh Berkus

-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 436-9166
   for law firms, small businesses       fax  436-0137
    and non-profit organizations.       pager 338-4078
                        		San Francisco