MySQL INNER JOIN select only one row from second table
You need to have a subquery to get their latest date per user ID.
SELECT a.*, c.*FROM users a INNER JOIN payments c ON a.id = c.user_ID INNER JOIN ( SELECT user_ID, MAX(date) maxDate FROM payments GROUP BY user_ID ) b ON c.user_ID = b.user_ID AND c.date = b.maxDateWHERE a.package = 1
SELECT u.*, p.*FROM users AS uINNER JOIN payments AS p ON p.id = ( SELECT id FROM payments AS p2 WHERE p2.user_id = u.id ORDER BY date DESC LIMIT 1)Or
SELECT u.*, p.*FROM users AS uINNER JOIN payments AS p ON p.user_id = u.idWHERE NOT EXISTS ( SELECT 1 FROM payments AS p2 WHERE p2.user_id = p.user_id AND (p2.date > p.date OR (p2.date = p.date AND p2.id > p.id)))These solutions are better than the accepted answer because they work correctly when there are multiple payments with same user and date. You can try on SQL Fiddle.
SELECT u.*, p.*, max(p.date)FROM payments pJOIN users u ON u.id=p.user_id AND u.package = 1GROUP BY u.idORDER BY p.date DESCCheck out this sqlfiddle