I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances.
I'm trying to run a query that will return an individual users 'amount owed' across all groups (to which they belong).
At the moment the query looks like this:
SELECT SUM(owe) FROM ( SELECT (expenses.amount/count(*)) AS owe FROM expenses LEFT JOIN user_expenses ON ( expenses.id = user_expenses.expense_id ) WHERE expenses.paid_by != 1 GROUP BY expenses.id ) AS total;
The problem is this query retrieves ALL expenses across the board. What i'd like to do is restrict by using:
WHERE user_expenses.user_id = 1
How can I add that to the aforementioned query (in a subquery) to restrict the result to expenses that only that user is associated with, but still get all users involved in an expense so i can do the math for amount each member owes?
TABLE SCHEMA (if that might help):
USERS id name username email EXPENSES id created_at updated_at title amount group_id paid_by img_url note USER_EXPENSES (join table) id expense_id user_id GROUPS id created_at name desc USER_GROUPS id user_id group_id
Some more sample info for clarification: if i run this query -
SELECT e.title, e.amount, (e.amount/count(*)) AS owe, count(*) AS members FROM expenses e LEFT JOIN user_expenses ue ON e.id = ue.expense_id WHERE e.paid_by != 1 GROUP BY e.id;
If I run the following:
SELECT e.title, e.amount, (e.amount/count(*)) AS owe, count(*) AS members FROM expenses e LEFT JOIN user_expenses ue ON e.id = ue.expense_id AND ue.user_id = 1 WHERE e.paid_by != 1 GROUP BY e.id;