Search code examples
mysqlsqlselectinner-join

MySQL INNER JOIN select only one row from second table

I have a users table and a payments table, for each user, those of which have payments, may have multiple associated payments in the payments table. I would like to select all users who have payments, but only select their latest payment. I'm trying this SQL but i've never tried nested SQL statements before so I want to know what i'm doing wrong. Appreciate the help

SELECT u.* 
FROM users AS u
    INNER JOIN (
        SELECT p.*
        FROM payments AS p
        ORDER BY date DESC
        LIMIT 1
    )
    ON p.user_id = u.id
WHERE u.package = 1
Solution

  • You need to have a subquery to get their latest date per user ID.

    SELECT  u.*, p.*
FROM users u 
    INNER JOIN payments p
        ON u.id = p.user_ID
    INNER JOIN
    (
        SELECT user_ID, MAX(date) maxDate
        FROM payments
        GROUP BY user_ID
    ) b ON p.user_ID = b.user_ID AND
            p.date = b.maxDate
WHERE u.package = 1