trying to get a count of transfers from one account to another account within the same bank
relevant columns:

table transfers
transferid, toaccount, fromaccount

table accounts
accountid, bank

what i have now is
select count(*) from transfers where
toaccount in (select accountid from accounts where bank = @bank)and
fromaccount in(select accountid from account where where bank = @bank)

this works but is extremely slow as the accounts table is large and this has to run through it twice.

any ideas for optimizing this query
also, I don't know if this will help, but all I really care about is if there are any or not, the exact count doesn't really matter

campkev,

I don't have much experience with Microsoft SQL Server, I use Oracle. However this is how I would approch it in Oracle. The syntax may be different in SQL Server but the principal will be the same.

select count(1)
from transfer t, account a1, account a2,
where t.toaccount = a1.accountid
and t.fromaccount = a2.accountid
and a1.bank = a2.bank

If there are appropiate indexes on the tables in question this should be fast.

By the way, using multiple 'in' statements like you posted is very expensive. Using joins is prefered.

Kate