How to achieve Mysql Union with left joins on two tables

Question

There is a deposit table

Then there is a withdraw table

I have to create a mysql query so that i have columns userid, name, email, total deposit, total withdraw and net (total deposit - total withdraw) for a given date range from the above two tables with status as Approved Ordered by on the basis of net

There will be records in withdraw table but no records in deposit table for a userid then total withdraw is 0 for that corresponding period and vice versa if there are records in deposit table but no records in withdraw table for a userid then for such users the total withdraw is 0 for that corresponding period

How to achieve this with MYSQL query

Answer 1

First, prepare a dataset that is the UNION of your two tables, but just including the 'Approved' records:

SELECT *, 'deposit' AS type FROM deposit WHERE status = 'Approved'
UNION ALL
SELECT *, 'withdraw' AS type FROM withdraw WHERE status = 'Approved'

Then you can select from this dataset, with some conditional sums:

WITH alltypes AS (
    SELECT *, 'deposit' AS type FROM deposit WHERE status = 'Approved'
    UNION ALL
    SELECT *, 'withdraw' AS type FROM withdraw WHERE status = 'Approved'
)
SELECT
    userid,
    name,
    email,
    SUM(if(type = 'deposit', amount, 0)) AS 'total deposit',
    SUM(if(type = 'withdraw', amount, 0)) AS 'total withdraw',
    SUM(if(type = 'deposit', amount, -1 * amount)) AS 'net'
FROM alltypes
GROUP BY userid, name, email

This gives:

+--------+------+---------------+---------------+----------------+------+
| userid | name | email         | total deposit | total withdraw | net  |
+--------+------+---------------+---------------+----------------+------+
|      1 | fine | fine@fine.com |           500 |             50 |  450 |
|      4 | new  | new@new.com   |            20 |             30 |  -10 |
+--------+------+---------------+---------------+----------------+------+