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