I need to join with last record of “purchased_products” table to get the latest actual balance.
SELECT * FROM {users} u
LEFT JOIN {role_expire} re ON u.uid=re.uid
LEFT JOIN {purchased_products} pp ON re.uid=pp.uid
WHERE (re.expire <= NOW() OR re.expire is NULL) || (pp.balance < 0 OR pp.balance IS NULL)
SELECT *
FROM {users} u
LEFT OUTER
JOIN {role_expire} re
ON re.uid = u.uid
AND re.expire <= NOW()
LEFT OUTER
JOIN ( SELECT uid
, MAX(purchase_date) AS last_date
FROM {purchased_products}
WHERE balance < 0
GROUP
BY uid ) AS xxx
ON xxx.uid = u.uid
LEFT OUTER
JOIN {purchased_products} AS pp
ON pp.uid = u.uid
AND pp.purchase_date = xxx.last_date
AND pp.balance < 0
WHERE re.expire <= NOW()
OR re.expire is NULL