PostgreSQL joining multiple table

Question

I have three tables user, deposit and withdraw.

user:

 | id | name    |
 | -: | :------ |
 |  1 | Lorem   |
 |  2 | Ipsum   |
 |  3 | Dolores |

deposit:

 user_id | amount
 ------: | -----:
       1 |   1000
       3 |   3000

withdraw:

 user_id | amount
 ------: | -----:
       1 |   2000
       2 |   3000

My question is how to join the table, expected result:

 id |  sum_deposit |  sum_withdraw
 -: | -----------: | ------------:
  1 |         1000 |          2000
  2 |         null |          3000
  3 |         3000 |          null

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Feb 9, 2021 at 7:32
Please correctly format your question and add what you've already tried and what result or error did you get. — endo64
– endo64, Commented Feb 9, 2021 at 7:46

S-Man · Accepted Answer · 2021-02-09 08:13:25Z

1

demo:db<>fiddle

First SUM() the deposits and withdraws data and then do the LEFT JOIN:

SELECT
    *
FROM users u

LEFT JOIN (
   SELECT 
       user_id,
       SUM(amount)
   FROM deposit
   GROUP BY user_id
) d ON u.id = d.user_id

LEFT JOIN (
   SELECT 
       user_id,
       SUM(amount)
   FROM withdraw
   GROUP BY user_id
) w ON u.id = w.user_id

answered Feb 9, 2021 at 8:13

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey · Accepted Answer · 2021-02-09 08:03:54Z

0

 WITH USERS AS 
  (
     SELECT 1 AS USER_ID,'LOREM' AS USER_NAME
         UNION ALL 
     SELECT 2 ,'IPSUM'
        UNION ALL
     SELECT 3,'DOLORES'
  ),
 DEPOSIT(ID,USER_ID,AMOUNT) AS  
  (
    SELECT 1,1,1000
       UNION ALL 
    SELECT 3,3,3000
  ),
WITHDRAW(ID,USER_ID,AMOUNT) AS  
 (
    SELECT 1,1,2000
      UNION ALL 
    SELECT 2,2,3000
 )
SELECT U.USER_ID,SUM(D.AMOUNT)AS DEPOSIT,
 SUM(W.AMOUNT)AS WITHDRAW
 FROM USERS AS U
 LEFT JOIN DEPOSIT AS D ON U.USER_ID=D.USER_ID
 LEFT JOIN WITHDRAW AS W ON U.USER_ID=W.USER_ID
 GROUP BY U.USER_ID;

Please try something like this

edited Feb 9, 2021 at 8:03

answered Feb 9, 2021 at 7:42

Sergey

5,2801 gold badge9 silver badges12 bronze badges

1 Comment

Andy Over a year ago

results of deposits and withdrawals do not match, if the deposit has 2 data and withdraws 1 data then it will get double data in withdraws

Collectives™ on Stack Overflow

PostgreSQL joining multiple table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related