Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    6

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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Jun 2004
    Posts
    6

    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 ?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah! I see this as a very different problem. I'd use something like:
    Code:
    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

  5. #5
    Join Date
    Jun 2004
    Posts
    6

    Solved!

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •