Re: Query Problem

What is the explain output for the queries you've tried?

Stephan Szabo
sszabo@bigpanda.com

On Wed, 25 Oct 2000, Josh Berkus wrote:

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