[orekin]

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?
Thanks, Bill

[Pat Phelan]

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.

-PatP

[orekin]

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 ?

[Pat Phelan]

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

-PatP

[orekin]

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"

Bill