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 ?
