1

There are there tables the below image.

enter image description here

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.

enter image description here

1
  • Can you post create table .. with some data at sqlfiddle.com/#!6. This will help us help you out
    – Kin Shah
    Jun 24, 2015 at 20:33

2 Answers 2

5

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.)

12
  • Thanks for the answer but answer is incorrect.. only display one person account value... i wanted to display the answer like the above 2nd image..
    – M.I.M.F
    Jun 24, 2015 at 20:27
  • 1
    Kenneth has it basically right. To fix the missing clients use LEFT JOIN instead of JOIN to avoid removing clients for whom there are no returned checks.
    – Chris
    Jun 24, 2015 at 20:43
  • YES Kenneths Answer is correct instead of using JOIN for LEFT JOIN.. and thanks Chris.. Any good solutions instead of this answer... and please explain the answer how answer came.
    – M.I.M.F
    Jun 24, 2015 at 20:59
  • 1
    LEFT JOIN is absolutely correct. Sorry about that. I read JOIN in the question and was going to mention the LEFT JOIN in the text but got in a hurry and forgot. Thanks for the catch @Chris ! Jun 24, 2015 at 21:02
  • @user1663855: There may be other ways to solve this query, but they aren't going to be "better" or much more compact than the answer Kenneth has given.
    – Chris
    Jun 24, 2015 at 21:08
0

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;

enter image description here

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

1
  • Thanks for the answer oNre.. but getting error... Incorrect syntax near 'IT'. AND Incorrect syntax near '0.00'. i'm using SQL Server 2008
    – M.I.M.F
    Jun 25, 2015 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.