mysql joining 3 tables with sum gives wrong result

Question

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.

I am expecting a result like:

supplier name----------------------- due------------------------------- payment

name------------------ sum (quantity*unitprice) ------------------sum (pay amount)

My query is

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

But the result is multiplying sum of due with the number of payment, and vice-versa.

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.

Please help. Thank you

Answer 1

You cannot join the stock and the payment-table. The join will combine every row in tbl_payment with every row in tbl_stock, so with 3 rows in tbl_payment, you would get the 1 original row in tbl_stock 3 times, and then sum over these. Distinct is NOT a solution, because it then ignores values that are, well, not distinct, e.g. if you actually have 2 payments of $100, you will have a distinct sum of $100 (since $100 and $100 are the same value).

You can use subquerys to calculate the sums separately (this is an implied join of tbl_supplier with tbl_payment and tbl_supplier with tbl_stocks, but not all three together):

SELECT 
   tbl_supplier.supplier_name AS supplier,
   (select sum(tbl_payment.pay_amount) 
    from tbl_payment 
    where tbl_payment.supplier_id = tbl_supplier.id) as payment,
   (select sum(tbl_stock.stock_qnt * tbl_stock.stock_up) 
    from tbl_stock
    where tbl_stock.supplier_id = tbl_supplier.id) as due
from tbl_supplier;