SUM for Multiple Table and Child Table

Question

I need to create JOIN syntax for this query in MySQL :

SELECT 
    TblSupplier.SupplierName AS SupplierName, 
    (SUM(TblBuy.TotalBuy) - SUM(TblReturn.TotalReturn)) AS Total 
    (SUM(TblPayment.TotalPaymentToday) - SUM(TblReturnItem.TotalReturnToday)) AS TotalPayment, 
    ((SUM(TblBuy.TotalBuy) - SUM(TblReturn.TotalReturn)) - (SUM(TblPayment.TotalPaymentToday) - SUM(TblReturnItem.TotalReturnToday))) AS Balance 
FROM ... 
GROUP BY TblSupplier.ID

but I don't know how to do it...

This is my ER Diagram

Example for my question:

INSERT INTO TableSupplier (ID, SupplierName) VALUES (1, 'Company A'), (2, 'Company B');

INSERT INTO TableBuy (ID, SupplierID, TotalBuy) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 50), (4, 2, 100), (5, 2, 50);

INSERT INTO TablePayment (ID, BuyID, TotalPaymentToday) VALUES (1, 1, 50), (2, 3, 50);

INSERT INTO TableReturn (ID, SupplierID, TotalReturn) VALUES (1, 1, 20), (2, 2, 30), (3, 2, 10);

INSERT INTO TableReturnItem (ID, ReturnID, TotalReturnToday) VALUES (1, 1, 10), (2, 2, 30);

I follow this example, I got this result:

mysql> select
-> ts.SupplierName AS SupplierName,
-> (SUM(tb.TotalBuy) - SUM(tr.TotalReturn)) AS Total,
-> (SUM(tp.TotalPaymentToday) - SUM(tri.TotalReturnToday)) AS TotalPayment,
-> ((SUM(tb.TotalBuy) - SUM(tr.TotalReturn)) -
-> (SUM(tp.TotalPaymentToday) - SUM(tri.TotalReturnToday))) AS Balance
-> from tablesupplier ts
-> inner join tablebuy tb on ts.id=tb.id
-> inner join tablereturn tr on ts.id=tr.id
-> inner join tablepayment tp on ts.id=tp.id
-> inner join tablereturnitem tri on ts.id=tri.id
-> group by ts.id;

SupplierName | Total | TotalPayment | Balance
-------------------------------------------------
Company A | 80 | 40 | 40
Company B | 170 | 20 | 150

The result I want is:

SupplierName | Total | TotalPayment | Balance
-------------------------------------------------
Company A | 280 | 40 | 240
Company B | 160 | 20 | 140

Answer 1

try this :

select 
ts.SupplierName AS SupplierName, 
(SUM(tb.TotalBuy) - SUM(tr.TotalReturn)) AS Total 
(SUM(tp.TotalPaymentToday) - SUM(tri.TotalReturnToday)) AS TotalPayment, 
((SUM(tb.TotalBuy) - SUM(tr.TotalReturn)) - 
(SUM(tp.TotalPaymentToday) -     SUM(tri.TotalReturnToday))) AS Balance 
from tablesupplier ts
inner join tablebuy tb on ts.id=tb.supplierid
inner join tablereturn tr on ts.id=tr.supplierid
inner join tablepayment tp on tb.id=tp.buyid
inner join tablereturnitem tri on tr.id=tri.returnid
group by ts.id