Subquery to get payments that are due

Question

Hi, I have two tables, one that stores user earnings and another that stores the user payment. What I would like to do is to get payments that are due based on a $50 minimum and only from their last payment.

Table (userEarnings) has the following columns

id ->autoincrement  
userid -> unique id for users  
earn ->the amount of money the user earn (25.00, 14.50..)  
date_added -> DATETIME column

The second table (userPayment) stores each time a user gets paid

pid->autoincrement  
uid ->unique user id, this is the same as userid from the above table  
paid_amt->payments the user received(500.00, 100.00...)  
paid_date ->the date the user was paid(DATETIME)

From those two tables, I would like to list the userid and a sum of the money owed since the last payment if the sum is greater than $50. I assume I will need to use a subquery and group but not sure where to begin.

Any help would be greatly appreciated. Thanks

Answer 1

Try this:

SELECT userEarnings.userid, SUM(earn) AS total_earn FROM (
    SELECT uid, MAX(paid_date) AS paid_date
    FROM userPayment
    GROUP BY uid) AS T1
RIGHT JOIN userEarnings ON T1.uid = userEarnings.userid
WHERE date_added > T1.paid_date OR T1.paid_date IS NULL
GROUP BY userEarnings.userid
HAVING total_earn > 50

Answer 2

SELECT userEarnings.userid, SUM(userEarnings.earn) AS earn
FROM userEarnings 
LEFT OUTER JOIN (
    SELECT uid, MAX(paid_date) AS last_payment
    FROM userPayment
    GROUP BY uid) AS q
ON q.uid = userEarnings.userid
--the q.last_payment IS NULL clause ensures that the user who never got a payment before will get his first $50 check
WHERE (userEarnings.date_added > q.last_payment) OR (q.last_payment IS NULL)
GROUP BY userEarnings.userid
HAVING SUM(userEarnings.earn) >= 50.0

Answer 3

Slightly different approach, using a not exists subquery. Can perform differently and might be easier to read, depending on taste.

SELECT    ue.userid User
,         SUM(ue.earn) AmountDue
FROM      userEarnings ue
WHERE     NOT EXISTS (
          SELECT *
          FROM userPayment up
          WHERE up.uid = ue.userid
          AND up.paid_date >= ue.date_added
          )
GROUP BY  ue.userid
HAVING    AmountDue > 50.0