Pierre-Frédéric Caillaud wrote:
Your suffering comes from the "where ba.bankaccountID =
u.bankaccountID" in the subselect. It means postgres has to run the
subselect once for each row in Users. You want the subselect to run
only once, and return one (or more?) bankaccountid's, then fetch the
users from Users.
Just remove the "where ba.bankaccountID = u.bankaccountID" !
select userID, fname, lname, email, phone, dateEntered, dateCanceled,
dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as
searches
from Users u
where bankaccountid in (select bankaccountid
from bankaccount ba
where ba.bankaccountID = u.bankaccountID
and ba.accountnumber = '12345678'
and ba.routingNumber = '12345678')
order by UserID desc
limit 500
New version :
select userID, fname, lname, email, phone, dateEntered, dateCanceled,
dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as
searches
from Users u
where bankaccountid in (select bankaccountid
from bankaccount ba
WHERE ba.accountnumber = '12345678'
and ba.routingNumber = '12345678')
You could also do this :
select u.* from Users u, bankaccount ba
where u.bankaccountid = ba.bankaccountid
and ba.accountnumber = '12345678'
and ba.routingNumber = '12345678')
Thanks! a lot that was it , it is way much better now.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])