i have 3 tables : users, deposit, withdraw
table users :
id , username, referral
- 1, a1, null
- 2, a2, a1
- 3, a3, a2
- 4, a4, a1
- 5, a5, a2
table deposit :
id, users_id, amount, status, approve_date
- 1, 1, 10000.00, approve, 2022-10-01 14:52:53
- 2, 3, 10000.00, approve, 2022-10-01 14:52:53
- 3, 3, 10000.00, approve, 2022-10-01 14:52:53
- 4, 3, 10000.00, approve, 2022-10-01 14:52:53
- 5, 5, 10000.00, approve, 2022-10-01 14:52:53
table withdraw :
id, users_id, amount, status, approve_date
- 1, 1, 20000.00, approve, 2022-10-01 14:52:53
- 2, 3, 10000.00, approve, 2022-10-01 14:52:53
- 3, 3, 30000.00, approve, 2022-10-01 14:52:53
- 4, 3, 40000.00, approve, 2022-10-01 14:52:53
- 5, 5, 100000.00, approve, 2022-10-01 14:52:53
I would like to have the below end result, how do I combine the 3 tables together? The end result is sorted by SUM(deposit.amount) SUM(withdraw.amount) and where by approve date and search by referral . I apologize for couldn't display table data in a nice format for easier viewing. Thank you for your help.
what is try is like this sql code :
SELECT a.`referral`, a.`id`, a.`username`, SUM(b.`amount`) AS Total,SUM(c.`amount`) AS Totals FROM users a
LEFT JOIN `deposit` b ON a.`id` = b.`user_id`
LEFT JOIN `withdraw` c ON a.`id` = c.`user_id`
WHERE 1
AND b.`approve_date` >= '2022-10-01 00:00:00'
AND b.`approve_date` <= '2022-11-04 23:59:59'
AND b.`status` = 'approve'
AND c.`status` = 'approve'
AND a.`referral` = 'a1'
GROUP BY b.user_id,c.user_id
ORDER BY a.`id` DESC
LIMIT 500 OFFSET 0
the result i want is like this:
username, total_deposit, total_withdraw
- a3, 30000, 80000