Having Trouble w/ Complex Query that uses SUM and JOINS 3 Tables

[nsstudio]

I'm having trouble getting a query to work, and would love some help... Basically, I'm trying to build a PHP "Accounts Receivables" report to see what clients owe money, and how much. I have three tables: "clients" with the client's ID, name, address, etc; "projects" which has a field called price, among others, and contains multiple projects per client; and "payments" which keeps track of all client payments and has an amount field, amont others.

I'm trying to have a report that lists the client ID, name, total price, and total payments. Then I'll have a separate PHP variable to subtract the total payments from the total price and display the balance.

Here is my current code... It works fine if I remove the third JOIN table and the SUM(payments.amount), but as soon as I add those, it multiplies the sum values of both price and payments by the number of returned rows, and all the numbers are inaccurate. For example, if the SUM(projects.price) "should" be $500 and there were two payments made for $250 each, it will list both sums as $1000.

Does anyone have any ideas? Below is my current query:


SELECT clients.id, clients.name, SUM(projects.price), SUM(payments.amount) FROM clients LEFT JOIN projects ON clients.id=projects.client_id LEFT JOIN payments ON projects.client_id=payments.client_id GROUP BY clients.id

[koencalliauw]

try experimenting with the DISTINCT keyword in MySQL (select distinct client.id, ...)

Koen

[nsstudio]

Thanks! I tried that, but no luck... I'm wondering if it has to do something with the type of join, or if I have to do a nested query... Anyone have any ideas?

[Barand]

If you have

[code]project
client   |   price
   1     |    500


payment
client  |  amount
  1     |    250
  1     |    250[/code]


then the joined result is
[code]client |  price  |  amount
  1    |   500   |   250
  1    |   500   |   250[/code]

So when you sum the price it doubles (or trebles if 3 payments etc)

So you need a subquery.

[nsstudio]

That's what I thought... So now my question is, can anyone help me with the subquery? I've seen them done, but never using a SUM field, and am not quite sure what order to put the queries in. Thanks again for all your help!

[Barand]

This should work even if client has multiple projects
[code]
SELECT client.id, client.name, SUM(project.price) AS tot_price, subq.tot_pay
FROM (client INNER JOIN project ON client.id = project.clientid)
INNER JOIN  (SELECT clientid, SUM(amount) as tot_pay FROM payment GROUP BY clientid) as subq
ON client.id = subq.clientid
GROUP BY client.id, client.name, subq.tot_pay;[/code]