How to get the latest value from the two tables. MySql-CodePudding

What I want to do is get the record of last_recharge of users based on these two tables

I Tried but got the duplicate recharge_order_id, only want the single record but based on last_recharge

Recharge Logs Table

| recharge_order_id | last_recharge       | code |
| ----------------- | --------------------| -----|
| 98989898          | 2022-03-09 13:18:56 | 1356 |
| 98989898          | 2022-02-08 17:34:56 | 1356 |
| 98984444          | 2022-01-08 19:21:56 | 2222 |
| 98985555          | 2022-04-02 21:56:56 | 4444 |
| 97979797          | 2022-04-06 23:56:56 | 1111 |

User Data

| id | username | recharge_order_id | code |
| -- | -------- | ------------------| -----|
| 1  | max_max  |  98989898         | 1356 |
| 2  | jac_jac  |  97979797         | 1111 |
| 3  | leo_leo  |  98984444         | 2222 |
| 4  | bob_bob  |  98985555         | 4444 |

Result I want

| id | username | recharge_order_id | code | last_recharge       |
| -- | -------- | ------------------| -----| ------------------- |
| 1  | max_max  |  98989898         | 1356 | 2022-03-09 13:18:56 |
| 2  | jac_jac  |  97979797         | 1111 | 2022-04-06 23:56:56 |
| 3  | leo_leo  |  98984444         | 2222 | 2022-01-08 19:21:56 |
| 4  | bob_bob  |  98985555         | 4444 | 2022-04-02 21:56:56 |

CodePudding user response：

SELECT id, username, user.recharge_order_id, user.code, 
    MAX(last_recharge) AS last_recharge
FROM user JOIN recharge_logs
    ON user.recharge_order_id = recharge_logs.recharge_order_id
GROUP BY id, username, user.recharge_order_id, user.code
ORDER BY id;

Based on the data provided, it looks like you could JOIN ON the code column too.