I have two tables that are many to many in mysql and I need to use a sum function on them.
SELECT c.CustomerID, c.FirstName, c.LastName, SUM(t.Amount) AS Current_Balance
FROM customers AS c, transactions AS t, bankaccounts AS a, has
#where clause used to link customers to the transactions table
WHERE c.CustomerID = has.CustomerID AND has.AccountID = a.AccountID AND a.AccountID = t.AccountID
#Groups all the customer ids together
GROUP BY a.AccountID
#only selects those with a balance greater than 5000
HAVING Current_Balance >= 5000;
In my code sample I get a link between the customer table and account table, which is the has table, and then get the relevant transactions from the transaction table. However when I choose to use group by the accountID, I found that my joint account (which is shared between two customers) seems to be counted twice as it appears in my having condition despite it's balance being less than 5000. Likewise trying group by customerID will add together all the transactions of each customers bank account. I want to have it so that only the one bank account will have it's transactions added together. What can I do to solve this issue?
