MySQL INNER JOIN select only one row from second table

mysql sql select inner-join
Wasim picture Wasim · Sep 21, 2012 · Viewed 212.8k times · Source

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

Answer

John Woo picture John Woo · Sep 21, 2012

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

Related questions

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
MySQL SELECT only not null values

Is it possible to do a select statement that takes only NOT NULL values? Right now I am using this: SELECT * FROM table And then I have to filter out the null values with a php loop. Is there a …

Read More
MySQL Select Query - Get only first 10 characters of a value

Ok, so here is the issue. I have a table with some columns and 'subject' is one of the columns. I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a string with 100 …

Read More