How to join these tables?

Question

I have three tables in a database, as follows:

users:

  user_id                     name              pbal

    1                          m1               100
    2                          m2               200
    3                          m3               300
    4                          m4               400
    5                          m5               500

payouts:

    id                       user_id           amount

    1                          1                100
    2                          1                200
    3                          2                300
    4                          1                400

blocked:

   id                          user_id           status
 ------                        --------          ------
    1                           2                 block
    2                           3                 block

Now I want to make a list that excludes users whose user_id is in the blocked table, and that calculates a new total amount based on the pbal from users and the amount from the related rows in payouts:

   name                 total_amount
   -----                ------------
    m1                    800
    m5                    500
    m4                    400

I have been trying for half an hour, but failed. Can someone help me?

Answer 1

Here's an example:

SELECT u.name, u.user_id, MAX(u.pbal) + SUM(p.amount) AS total_amount
FROM dbo.Payouts p 
INNER JOIN dbo.Users u ON u.user_id = p.user_id
LEFT OUTER JOIN dbo.Blocked b ON u.user_id = b.user_id
WHERE b.user_id IS NULL
GROUP BY u.user_id, u.name
ORDER BY MAX(u.pbal) + SUM(p.amount) DESC;