MySQL INNER JOIN select only one row from second table

Question

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

score 200 · Accepted Answer · edited Feb 14 '23 at 13:18

200

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

edited Feb 14 '23 at 13:18

doppelgreener

4,809
10
46
63

answered Sep 21 '12 at 07:45

John Woo

258,903
69
498
492

1

@Fluffeh you have a very nice answer `Part 1 - Joins and Unions`. :) bookmarked! – John Woo Sep 21 '12 at 07:50
1

Thanks mate, wrote it for just these sort of situations, pop up a quick answer with the right SQL, then suggest a link to that monster indepth read so that folks can *understand* the suggested code. Planning on adding to it to further expand it. Welcome to join in if you like, I should pop up a fiddle for the code really... – Fluffeh Sep 21 '12 at 07:52
@JohnWoo thanks for your answer, that has worked perfectly. And thanks Fluffeh for the Q&A, i'll take a look into it! – Wasim Sep 21 '12 at 07:56
I think that my answer is more simple and is faster because I'm using just one inner join. Maybe I'm wrong? – Mihai Matei Sep 21 '12 at 08:01
@MateiMihai [check this link](http://sqlfiddle.com/#!2/1d554/1) added record for user 1 – John Woo Sep 21 '12 at 08:03
ohh.. then seems that order by is not working properly with this method.. thanks.. I didn't know – Mihai Matei Sep 21 '12 at 08:05
@JohnWoo Thanks for this. It helped me to solve a similar problem :) – Lucas Oct 08 '14 at 12:30
You'll probably want to make sure you have an index on payments.date too. – btsai Mar 01 '16 at 13:30
Hi John, if the payments table contain two rows of date returned by MAX, this query still returns two rows. Is there a work around to still return 1 row? Thanks a lot in advance! – Prashant Agarwal Jan 11 '17 at 19:18
2

Is there a way to do this query without the inner joins? I want to return the max from table c OR get null back if no rows match. Changing the JOIN to LEFT JOIN doesn't work obviously. – Scott Sep 25 '17 at 23:57

Finesse · Answer 2 · 2021-07-07T02:59:37.637

58

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.

edited Jul 07 '21 at 02:59

answered Jul 05 '18 at 11:23

Finesse

9,793
7
62
92

1

it is a good approach you use. but i have question about fetching one image against one product like. One product has many (4) images but i only want to show only one image against that product. – Ali Raza Jul 22 '19 at 21:13
Don't you think it will be very slow to use? as you are processing for every record in your sub query for inner join. Accepted answer can be the best possible answer after a minor tweak. – Hamees A. Khan Jul 30 '20 at 05:22
@HameesA.Khan, I haven't examined the queries execution. You may be right, but the accepted solution makes a 3-dimentional join which can be slow too. I'm afraid the tweak won't be _minor_ (this is the subject of the question). – Finesse Jul 30 '20 at 13:18
As a fact this helped me for another task. voted up. Good knowledge of SQL, great thanks. Respect. – Archil Labadze Oct 31 '21 at 08:52

score 11 · Answer 3 · edited Apr 28 '15 at 10:45

11

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

edited Apr 28 '15 at 10:45

shodanshok

167
11

answered Sep 21 '12 at 07:44

Mihai Matei

24,166
5
32
50

6

The `limit 1` clause will only return 1 user which isn't what the OP wants. – Fluffeh Sep 21 '12 at 07:45
8

@MateiMihai , this don't work. The query gives only max date, not whole row with max date. You can see it in fiddle: `date` column is different from `max(p.date)`. If you add more columns in `payments` table (e.g. `cost`), all that columns will be not from needed row – zxcat Aug 23 '15 at 11:03
Good fix for me, worked like a charm. – James Osguthorpe Nov 24 '22 at 12:30

score 3 · Answer 4 · answered Sep 21 '12 at 07:55

   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

score 3 · Answer 5 · edited Oct 10 '19 at 12:35

3

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

edited Oct 10 '19 at 12:35

Dino

7,779
12
46
85

answered Oct 10 '19 at 12:09

Shekhar

51
2

2

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Alessio Oct 10 '19 at 12:26

score 2 · Answer 6 · answered Sep 21 '12 at 07:47

2

There are two problems with your query:

Every table and subquery needs a name, so you have to name the subquery INNER JOIN (SELECT ...) AS p ON ....
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

answered Sep 21 '12 at 07:47

lc.

113,939
20
158
187

it is a good approach you use. but i have question about fetching one image against one product like. One product has many (4) images but i only want to show only one image against that product. – Ali Raza Jul 22 '19 at 21:14
I found this most fast in my case. The only change I did is added a where clause in sub query to filter selected user data only `From payments as p Where p.user_id =@user_id` as the query is doing a group by on whole table. – Vikash Rathee Dec 22 '19 at 05:33

score 2 · Answer 7 · answered Oct 12 '16 at 13:14

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

score 2 · Answer 8 · answered Mar 30 '20 at 09:29

2

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

answered Mar 30 '20 at 09:29

Justice Eziefule

31
4

score 1 · Answer 9 · edited Oct 29 '14 at 14:17

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.

edited Oct 29 '14 at 14:17

KyleMit

30,350
66
462
664

answered Aug 25 '14 at 13:27

Hassan Dad Khan

645
1
6
23

score 1 · Answer 10 · answered May 30 '18 at 08:24

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

score 1 · Answer 11 · edited Jun 15 '21 at 04:32

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

edited Jun 15 '21 at 04:32

Aaron J Spetner

2,117
1
18
30

answered Nov 09 '20 at 04:37

shaharyar

59
6

score 0 · Answer 12 · answered Jan 08 '23 at 14:53

If you do not have to return the payment from the query you can do this with distinct, like:

SELECT DISTINCT u.* 
FROM users AS u
INNER JOIN payments AS p ON p.user_id = u.id

This will return only users which have at least one record associated in payment table (because of inner join), and if user have multiple payments, will be returned only once (because of distinct), but the payment itself won't be returned, if you need the payment to be returned from the query, you can use for example subquery as other proposed.

MySQL INNER JOIN select only one row from second table

12 Answers12

Linked

Related