CODEWITHSUNDEEP
mysqlsql
Uma
Uma

Reputation: 239

Joining two mysql resultset using left outer join

Below is the actual invoice table

enter image description here

After grouping it based on invoiceID, the resultset is enter image description here

And actual payment table is

enter image description here

and its payment resultset after grouping based on invoiceID is

enter image description here

Now i want to join these two resultsets [Payment and Invoice table] and find the balance amount subtracting Total from Amount based on InvoiceID and for non matching records the balance column should be zero. I tried this, but didn't get the expected result.

Upvotes: 1

Views: 324

Answers (1)

John Woo
John Woo

Reputation: 263853

Try something like this,

SELECT  a.InvoiceID,
        a.totalSum InvoiceAmount,
        b.totalSum PaymentAmount,
        a.totalSum - COALESCE(b.totalSum, 0) TotalBalance
FROM
    (
        SELECT  InvoiceID, SUM(Total) totalSum
        FROM    InvoiceTB
        GROUP BY InvoiceID
    ) a LEFT JOIN
    (
        SELECT  InvoiceID, SUM(Total) totalSum
        FROM    paymentTB
        GROUP BY InvoiceID
    ) b
        ON a.InvoiceID = b.InvoiceID

Upvotes: 1

Related Questions