How to join these tables?

  • A+
Category:Languages

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?

 


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; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: