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

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

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))
)

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

   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

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

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

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

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

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.

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

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