How get the sum of two tables value using inner join

Question

There are there tables the below image.

My question is retrieve from each customers and get the sum of Invoice total due, Invoice Total balance in invoice table and return cheque total due and return cheque total balance in return cheque table...

I wanted the answer like below image from the above 3 table using inner join

Please help me thank you.

Answer 1

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName, i.InvoiceTotalDue, i.InvoiceTotalBalance, r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance
FROM Client_TBL c
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue, SUM(TotalBalance) AS InvoiceTotalBalance
FROM Invoice_TBL
GROUP BY ClientNo
) AS i ON c.ClientNo = i.ClientNo
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue, SUM(TotalBalance) AS ReturnChequeTotalBalance
FROM ReturnCheque_TBL
GROUP BY ClientNo
) AS r ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

Answer 2

I was trying another way, and here what I got:

    SELECT 
        ClientNo,
        ClientID,
        FullName,
        SUM(InvoiceTotalDue) as InvoiceTotalDue,
        sum(InvoiceTotalBalance) as InvoiceTotalBalance,
        sum(ReturnChequeTotalDue) as ReturnChequeTotalDue,
        sum(ReturnChequeTotalBalance) as ReturnChequeTotalBalance
    FROM (SELECT 
                CT.ClientNo,
                CT.ClientID,
                CT.FullName, #IT.*,RCT.* 
                IT.TotalDue as InvoiceTotalDue,
                IT.TotalBalance as InvoiceTotalBalance,
                0.00 as ReturnChequeTotalDue,
                0.00 as ReturnChequeTotalBalance  
            FROM Client_TBL as CT
            JOIN Invoice_TBL as IT on (IT.ClientNo=CT.ClientNo)

            UNION
            SELECT 
                CT.ClientNo,
                CT.ClientID,
                CT.FullName, #IT.*,RCT.* 
                0.00 as InvoiceTotalDue,
                0.00 as InvoiceTotalBalance,
                IFNULL((RCT.TotalDue),0.00) as ReturnChequeTotalDue,
                IFNULL((RCT.TotalBalance),0.00) as ReturnChequeTotalBalance   
            FROM Client_TBL as CT
            JOIN ReturnCheque_TBL as RCT on (RCT.ClientNo=CT.ClientNo)) AS ResultSet
    GROUP BY ClientNo;

If you want you can create a stored procedure to filter any information that you want.