Left join with last record

[Mikle]

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)

What is the best way to that?

[r937]

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