var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Left Outer Join but right table has > 1 record
Hi, my problem is how to link an Invoice table to a CreditNote table. Primary Keys are 'Invoice #' and 'CreditNote #' respectively.
Every Credit Note relates to a specific Invoice, so tables are joined by 'Invoice #'
Not every Invoice has a Credit Note ..... however it cannot be assumed that when an Invoice has a Credit Note, there will only be one of them. There could be > 1 Credit Notes against an Invoice.
How to join these tables correctly, using SQL query language?
The LEFT OUTER JOIN should do exactly what you want. If there are six credit notes, there should be six rows in the result set. If there is one credit note, there should be one row in the result set. If there are no credit notes, there should still be one row in the result set, but the credit note columns will all be NULL.
Summing the credit note amounts
Thanks Pat, I don't think I explained it clearly enough.
Lets take one example, say an invoice is for $100, and it has 2 credit notes, $10 and $20
If I was returning an Sales - Returns number on each row:
100 - 10 = 90
100 - 20 = 80
And if I then tried to sum my net sales I have the incorrect total of 170 instead of 70.
I need to to only count Sales once and then sum the credit note amounts, how would I go about that ?
Ah! I see this as a very different problem. I'd use something like:
SELECT i.invoice_id, i.amount - Coalesce(Sum(c.amount), 0) AS netAmount
FROM invoice AS i
LEFT OUTER JOIN CreditNote AS c
ON (c.invoiceId = i.invoiceId)
GROUP BY i.invoiceId, i.amount
Thanks Pat. I could not use COALESCE but used ISNULL instead:
SELECT "OEINVH"."INVNUMBER", "OEINVH"."INVNET" - IFNULL(SUM("OECRDH"."CRDNET"),0) AS "NETAMOUNT"
FROM "OEINVH" LEFT OUTER JOIN "OECRDH" ON "OECRDH"."INVNUMBER" = "OEINVH"."INVNUMBER"
GROUP BY "OEINVH"."INVNUMBER", "OEINVH"."INVNET"