[mysql] 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

This question is related to mysql sql select inner-join

The answer is


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.maxDate
WHERE a.package = 1

This is quite simple do The inner join and then group by user_id and use max aggregate function in payment_id assuming your table being user and payment query can be

select user.id, max(payment.id) from user inner join payment on (user.id = payment.user_id) group by user.id


You can try this:

SELECT u.*, p.*
FROM users AS u LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY [Date] DESC) AS RowNo
    FROM payments  
) AS p ON u.userid = p.userid AND p.RowNo=1

SELECT u.*, p.*
FROM users AS u
INNER 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 u
INNER JOIN payments AS p ON p.user_id = u.id
WHERE 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))
)

This 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.


There are two problems with your query:

  1. Every table and subquery needs a name, so you have to name the subquery INNER JOIN (SELECT ...) AS p ON ....
  2. The subquery as you have it only returns one row period, but you actually want one row for each user. For that you need one query to get the max date and then self-join back to get the whole row.

Assuming there are no ties for payments.date, try:

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

   SELECT u.* 
        FROM users AS u
        INNER JOIN (
            SELECT p.*,
             @num := if(@id = user_id, @num + 1, 1) as row_number,
             @id := user_id as tmp
            FROM payments AS p,
                 (SELECT @num := 0) x,
                 (SELECT @id := 0) y
            ORDER BY p.user_id ASC, date DESC)
        ON (p.user_id = u.id) and (p.row_number=1)
        WHERE u.package = 1

SELECT U.*, V.* FROM users AS U 
INNER JOIN (SELECT *
FROM payments
WHERE id IN (
SELECT MAX(id)
FROM payments
GROUP BY user_id
)) AS V ON U.id = V.user_id

This will get it working


My answer directly inspired from @valex very usefull, if you need several cols in the ORDER BY clause.

    SELECT u.* 
    FROM users AS u
    INNER JOIN (
        SELECT p.*,
         @num := if(@id = user_id, @num + 1, 1) as row_number,
         @id := user_id as tmp
        FROM (SELECT * FROM payments ORDER BY p.user_id ASC, date DESC) AS p,
             (SELECT @num := 0) x,
             (SELECT @id := 0) y
        )
    ON (p.user_id = u.id) and (p.row_number=1)
    WHERE u.package = 1

Matei Mihai given a simple and efficient solution but it will not work until put a MAX(date) in SELECT part so this query will become:

SELECT u.*, p.*, max(date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id

And order by will not make any difference in grouping but it can order the final result provided by group by. I tried it and it worked for me.


SELECT u.*, p.*, max(p.date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id
ORDER BY p.date DESC

Check out this sqlfiddle


@John Woo's answer helped me solve a similar problem. I've improved upon his answer by setting the correct ordering as well. This has worked for me:

SELECT  a.*, c.*
FROM users a 
    INNER JOIN payments c
        ON a.id = c.user_ID
    INNER JOIN (
        SELECT user_ID, MAX(date) as maxDate FROM
        (
            SELECT user_ID, date
            FROM payments
            ORDER BY date DESC
        ) d
        GROUP BY user_ID
    ) b ON c.user_ID = b.user_ID AND
           c.date = b.maxDate
WHERE a.package = 1

I'm not sure how efficient this is, though.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to inner-join

Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working Multiple INNER JOIN SQL ACCESS How to select all rows which have same value in some column Eliminating duplicate values based on only one column of the table How can I delete using INNER JOIN with SQL Server? How to use mysql JOIN without ON condition? Inner join with 3 tables in mysql SQL Inner join more than two tables MySQL INNER JOIN select only one row from second table Insert using LEFT JOIN and INNER JOIN