Member Avatar for RC_820
RC_820 0 Newbie Poster

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
Edited by RC_820 because: need more info
  • 1 Contributor
  • 0 Replies
  • 123 Views

Dani AI

Generated

— a few points to clarify and fix so the totals come out correctly.

Your query places deposit/withdraw filters in the WHERE clause and joins both raw tables to users. That (1) effectively turns the LEFT JOINs into INNER JOINs when a side is missing, and (2) can inflate sums by cross-multiplying deposit rows with withdraw rows before aggregation. The safer pattern is to aggregate each activity per user first (apply status/date filters there), then LEFT JOIN those aggregated results to users. Use COALESCE (or IFNULL) to show zeros instead of NULL and ORDER BY the aggregated aliases.

A typical solution (derived-table approach) looks like this:

SELECT u.id, u.username,
       COALESCE(d.total_deposit,0) AS total_deposit,
       COALESCE(w.total_withdraw,0) AS total_withdraw
FROM users u
LEFT JOIN (
  SELECT user_id, SUM(amount) AS total_deposit
  FROM deposit
  WHERE status='approve' AND approve_date BETWEEN '2022-10-01' AND '2022-11-04 23:59:59'
  GROUP BY user_id
) d ON d.user_id = u.id
LEFT JOIN (
  SELECT user_id, SUM(amount) AS total_withdraw
  FROM withdraw
  WHERE status='approve' AND approve_date BETWEEN '2022-10-01' AND '2022-11-04 23:59:59'
  GROUP BY user_id
) w ON w.user_id = u.id
WHERE u.referral = 'a1'
ORDER BY total_deposit DESC, total_withdraw DESC
LIMIT 500;

If you intend to include multi-level referrals (grandchildren of a referrer) — which would explain why a3 appears in your expected output — use a recursive CTE (MySQL 8+) to build the downline first, then join the aggregated totals to that set.

Also: add covering indexes on (user_id, status, approve_date) for deposit and withdraw; and be mindful of ONLY_FULL_GROUP_BY rules if you change grouping. For JOIN semantics and recursive CTEs, see the MySQL docs on JOINs and WITH/recursive queries: JOINs and WITH / recursive CTEs.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.