Re: Query Problem

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_BWHERE Table_B.GroupID IN (               SELECT GroupID FROM Table_C
WHERETableC.AccountID = 11))
 


Tomas