Re: How to optimize this query ?

Actually, I have got another query where I need to remove the subqueries
... It is almost the same query but this time, I don't search in the
table LEAD but in the table MEMBER .

HERE are the tables:
affiliate_lockout (6 rows) (member_id, affiliate_id)
automated (4 rows) (member_id, .)
lead (4490 rows) (id, ...)
member (6 rows) (id, ...)
member_exclusion (3 rows) (member_id, member_id_to_exclude)
purchase (10000 rows) (lead_id, member_id, ...)

----------------------------
select member.id, automated.delivery, member.email

from (automated INNER JOIN member ON member.id = automated.member_id)

where activated=1
and website='$SITE_NAME'
and (select count(*) from trans_member where
(unix_timestamp(now())-unix_timestamp(date)) <
(unix_timestamp(now())-'$today_midnight') and type='purchase' and
comment LIKE '%automated%'
and member_id=member.id and comment LIKE '%$type%') < max_$field
and balance_in_points > $price
and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%'
and states LIKE '%$lead[prop_state]%'
and ltv_t$n/100 >= (cast($lead[loan_amount] as unsigned) /
cast($lead[current_value] as unsigned))
and amount_t$n < $lead[loan_amount]

AND $id NOT IN (select lead_id from purchase where
member_id=member.id)
AND $aff_id NOT IN (select affiliate_locked_id from affiliate_lockout
where member_id=member.id)
AND $id NOT IN (select lead_id from purchase where member_id IN
(select member_id_to_exclude from member_exclusion where
member_id=member.id))

ORDER BY balance_in_points DESC
--------------------

For this one, I really don't know how to remove the 3 subqueries at the
end because the $id and $aff_id are values external to the query, and
there is no table to join .

I tried to remove the subqueries and to rewrite them to 3 small external
queries that I run for each result given by this first query, but I
guess this will be much longer if the tables are big .

What do you think about ?