Author: RC_820

i have 3 tables : users, deposit, withdraw
table users :

id , username, referral

1. 1, a1, null
2. 2, a2, a1
3. 3, a3, a2
4. 4, a4, a1
5. 5, a5, a2

table deposit :

id, users_id, amount, status, approve_date

1. 1, 1, 10000.00, approve, 2022-10-01 14:52:53
2. 2, 3, 10000.00, approve, 2022-10-01 14:52:53
3. 3, 3, 10000.00, approve, 2022-10-01 14:52:53
4. 4, 3, 10000.00, approve, 2022-10-01 14:52:53
5. 5, 5, 10000.00, approve, 2022-10-01 14:52:53

table withdraw :

id, users_id, amount, status, approve_date

1. 1, 1, 20000.00, approve, 2022-10-01 14:52:53
2. 2, 3, 10000.00, approve, 2022-10-01 14:52:53
3. 3, 3, 30000.00, approve, 2022-10-01 14:52:53
4. 4, 3, 40000.00, approve, 2022-10-01 14:52:53
5. 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

1. a3, 30000, 80000

hii how can i make self join and sum data in other table by username
i have table like this

members (id, username, referral)
ref_bonus (id, username, bonus)

members
id  | username | referral
----------------------
1    id1        id2
2    id2        null
3    id3        id2

ref_bonus
id | username | bonus
----------------------
11    id2      1.00
21    id2      1.00
31    id2      1.00

the result i want is select all member have referral and sum bonus in ref_bonus
example result is like this :

id  | username | count_ref | sum_bonus
----------------------
1    d2          2         | 3.0