title: mysql joining 3 tables with sum gives wrong result
body: <p>I have been trying to make a balance sheet, using 3 tables. First table is names of suppliers. The second table for is due to suppliers and third table is payment to supplier, both connected with a foreign key with supplier table.</p>
  
  <p>I am expecting a result like:</p>
  
  <blockquote>
    <p>supplier name-----------------------  due------------------------------- payment</p>
    
    <p>name------------------ sum (quantity*unitprice)
    ------------------sum (pay amount)</p>
  </blockquote>
  
  <p>My query is </p>
  
  <pre><code>SELECT 
      tbl_supplier.supplier_name AS supplier,
      SUM(tbl_payment.pay_amount) AS payment,
      SUM(tbl_stock.stock_qnt * tbl_stock.stock_up) AS due
  FROM
      tbl_supplier
          INNER JOIN
      tbl_payment ON tbl_supplier.id = tbl_payment.supplier_id
          INNER JOIN
      tbl_stock ON tbl_supplier.id = tbl_stock.supplier_id
  GROUP BY supplier_name
  </code></pre>
  
  <p><br></p>
  
  <p>But the result is multiplying sum of due with the number of payment, and vice-versa.</p>
  
  <p>For example, if a supplier has total due of 1000 and he has been paid 3 times, its showing total due is 3000, not 1000.</p>
  
  <p>Please help. Thank you</p>