0

Regarding Invoice Entry, i have make an invoice with 4 items invoice id is 13 Once i select the invoice No 13. without getting any payment part query retrieving exact answer the bellow screen shot and query

enter image description here

Query for the above Table design to retrieve records

SELECT BASE_Invoice.InvoiceId, BASE_Invoice.InvoiceDate, BASE_Invoice.PurchasingOrderNo, (CASE WHEN (BASE_Invoice.CustomerId IS NULL) THEN BASE_Invoice.CustomerId ELSE BASE_Customer.CustomerId END) AS CustomerId, (CASE WHEN (BASE_Invoice.CustomerId IS NULL) THEN BASE_Invoice.CustomerName ELSE BASE_Customer.CustomerName END) AS CustomerName, BASE_Invoice.InvoiceMethod, BASE_Invoice.Note, 
BASE_Inventory.ItemNameCode, BASE_Category.CategoryId, BASE_Category.CategoryName, BASE_Inventory.ItemDescription, BASE_Unit.UnitId, BASE_Unit.UnitName, BASE_InvoiceLine.WarrantyDays, 
BASE_InvoiceLine.Quantity, BASE_InvoiceLine.CostPrice, BASE_InvoiceLine.SellingPrice, BASE_InvoiceLine.ItemDiscountPercentage, BASE_InvoiceLine.ItemDiscountAmount, 
BASE_InvoiceLine.TotalItemDiscount, BASE_InvoiceLine.TotalAmount
FROM BASE_Category RIGHT OUTER JOIN
BASE_Invoice INNER JOIN
BASE_InvoiceLine ON BASE_Invoice.InvoiceId = BASE_InvoiceLine.InvoiceId INNER JOIN
BASE_Inventory ON BASE_InvoiceLine.InventoryId = BASE_Inventory.InventoryId LEFT OUTER JOIN
BASE_Customer ON BASE_Invoice.CustomerId = BASE_Customer.CustomerId ON BASE_Category.CategoryId = BASE_Inventory.CategoryId LEFT OUTER JOIN
BASE_Unit ON BASE_Inventory.UnitId = BASE_Unit.UnitId
WHERE (BASE_Invoice.InvoiceId = 13)

Result:

enter image description here

ISSUE:

When i'm retrieving invoice data with payment details (payment tables such as cash, card, bank deposit and cheque) getting duplicate result from the above..

the bellow screen shot and query

Table structure (Table design):

enter image description here

Query for the above Table design to retrieve records (Issue: duplicate resutlts)

SELECT BASE_Invoice.InvoiceId, BASE_Invoice.InvoiceDate, BASE_Invoice.PurchasingOrderNo, (CASE WHEN (BASE_Invoice.CustomerId IS NULL) THEN BASE_Invoice.CustomerId ELSE BASE_Customer.CustomerId END) AS CustomerId, (CASE WHEN (BASE_Invoice.CustomerId IS NULL) THEN BASE_Invoice.CustomerName ELSE BASE_Customer.CustomerName END) AS CustomerName, BASE_Invoice.InvoiceMethod, BASE_Invoice.Note, 
BASE_Invoice.GrossAmount, BASE_Invoice.TotalDiscount, BASE_Invoice.NetAmount, BASE_Invoice.InvoiceBalance, BASE_InvoiceLine.InvoiceLineId, BASE_Inventory.InventoryId, 
BASE_Inventory.ItemNameCode, BASE_Category.CategoryId, BASE_Category.CategoryName, BASE_Inventory.ItemDescription, BASE_Unit.UnitId, BASE_Unit.UnitName, BASE_InvoiceLine.WarrantyDays, 
BASE_InvoiceLine.Quantity, BASE_InvoiceLine.CostPrice, BASE_InvoiceLine.SellingPrice, BASE_InvoiceLine.ItemDiscountPercentage, BASE_InvoiceLine.ItemDiscountAmount, 
BASE_InvoiceLine.TotalItemDiscount, BASE_InvoiceLine.TotalAmount, BASE_InvoicePaymentLine.InvoicePaymentLineId, BASE_InvoicePaymentLine.Amount AS AdvancePayment, BASE_InvoicePayment.InvoicePaymentId, 
BASE_InvoicePayment.PaymentDate, BASE_InvoicePayment.InvoicePaymentMethod, BASE_InvoicePayment.IsCashPayment, BASE_CashInvoicePayment.CashInvoicePaymentId, 
BASE_CashInvoicePayment.CashAmount, BASE_CashInvoicePayment.CashReceived, BASE_CashInvoicePayment.CashBalance, BASE_InvoicePayment.IsCardPayment, 
BASE_CardInvoicePayment.CardInvoicePaymentId, BASE_CardInvoicePayment.CardType, BASE_CardInvoicePayment.CardNumber, BASE_CardInvoicePayment.Amount AS CardAmount, 
BASE_InvoicePayment.IsBankDepositPayment, BASE_BankDepositInvoicePayment.BankDepositInvoicePaymentId, BASE_BankDepositInvoicePayment.DepositDate, 
BASE_BankDepositInvoicePayment.NameOfBank AS BankDepositNameOfBank, BASE_BankDepositInvoicePayment.Branch AS BankDepositBranch, BASE_BankDepositInvoicePayment.AccountHolder, BASE_BankDepositInvoicePayment.AccountNumber, 
BASE_BankDepositInvoicePayment.Amount AS BankDepositAmount, BASE_InvoicePayment.IsChequePayment, BASE_ChequeInvoicePayment.ChequeInvoicePaymentId, BASE_ChequeInvoicePayment.ChequeDate, 
BASE_ChequeInvoicePayment.Narration, BASE_ChequeInvoicePayment.NameOfBank AS ChequeNameOfBank, BASE_ChequeInvoicePayment.Branch AS ChequeBranch, BASE_ChequeInvoicePayment.ChequeNumber, 
BASE_ChequeInvoicePayment.Amount AS ChequeAmount
FROM BASE_Category RIGHT OUTER JOIN
BASE_CashInvoicePayment RIGHT OUTER JOIN
BASE_Invoice INNER JOIN
BASE_InvoiceLine ON BASE_Invoice.InvoiceId = BASE_InvoiceLine.InvoiceId INNER JOIN
BASE_InvoicePaymentLine ON BASE_Invoice.InvoiceId = BASE_InvoicePaymentLine.InvoiceId INNER JOIN
BASE_InvoicePayment ON BASE_InvoicePaymentLine.InvoicePaymentId = BASE_InvoicePayment.InvoicePaymentId INNER JOIN
BASE_Inventory ON BASE_InvoiceLine.InventoryId = BASE_Inventory.InventoryId LEFT OUTER JOIN
BASE_BankDepositInvoicePayment ON BASE_InvoicePayment.InvoicePaymentId = BASE_BankDepositInvoicePayment.InvoicePaymentId LEFT OUTER JOIN
BASE_ChequeInvoicePayment ON BASE_InvoicePayment.InvoicePaymentId = BASE_ChequeInvoicePayment.ChequeInvoicePaymentId LEFT OUTER JOIN
BASE_CardInvoicePayment ON BASE_InvoicePayment.InvoicePaymentId = BASE_CardInvoicePayment.InvoicePaymentId ON 
BASE_CashInvoicePayment.InvoicePaymentId = BASE_InvoicePayment.InvoicePaymentId LEFT OUTER JOIN
BASE_Unit ON BASE_Inventory.UnitId = BASE_Unit.UnitId ON BASE_Category.CategoryId = BASE_Inventory.CategoryId LEFT OUTER JOIN
BASE_Customer ON BASE_Invoice.CustomerId = BASE_Customer.CustomerId
WHERE (BASE_Invoice.InvoiceId = 13)

Result:

enter image description here

1 Answer 1

1

I would need to know more about your requirements. Are you trying to report on payments, invoice lines, invoices, etc.

This may be a misunderstanding of the problem given the limited amount of information but...

It appears you are trying to return a single row for Invoice number 13 however you are including values from other tables which have multiple lines. An invoice may only be a single header line but the invoice lines (I assume products or services of some sort), can have multiples. Also, payments may have multiples. This explanation applies to any table where there is a 1:many relationship with your base table. For every table you join to your first table, if that second table has more than one row that matches on your join 'ON' statement, the number of result records will be duplicated.
This is why I asked the first question. What are you actually trying to report against?
If you just want a single record for each invoice and the tables that contain your other supporting data, i.e. payment totals, exist in another table that may contain more than one row, you will need to aggregate the results from that payment table.

So the first part of your answer will require you to use the SQL GROUP BY clause.

One other problem I am seeing is more of a functional approach issue. I'm noticing you are using RIGHT OUTER JOINS, INNER JOINS, and LEFT OUTER JOINS all in the same query. This is generally not recommended. There are certain situations where it makes sense but in this case, I think you are just confused.

To make things easier, start with the table that will represent your reporting record. In this case, it sounds like the Base_Invoice table. Any table you know will have a 1:1 with that table, meaning there will always be 1 record in the table you are joining to use an INNER JOIN. On the tables that may or may not have a record to match with, use a LEFT JOIN.

Then, once you have your joins simplified, add the GROUP BY to the bottom. GROUP BY BASE_Invoice.InvoiceId, BASE_Invoice.InvoiceDate, BASE_Invoice.PurchasingOrderNo, BASE_Invoice.CustomerId, BASE_Customer.CustomerId ... etc

Anything that is a 1:1 relationship back to the Invoice table can be listed in the GROUP BY and in the SELECT just like normal.
Anything that will be an aggregation, like the BASE_InvoiceLine.TotalItemDiscount will need to be aggregated in some way (SUM, AVG, MIN, MAX, etc). So in the select statement if you wanted to show the sum of all TotalItemDiscounts it would be SUM(BASE_InvoiceLine.TotalItemDiscount).

3
  • i get your answer. The thing is Invoice and InvoiceLine part is ok for that i want to retrieve payment for example for one invoice getting more than one payment through cash, card, bank deposit and cheque at the time i want to retrieve what are the payments made ex: Total net amount is 1000 cash paying 200 like wise cheque paying 800 in that 800 3 cheques are there so in need to display what are the payments made detaild... pls help me.
    – M.I.M.F
    Aug 24, 2016 at 4:06
  • I want Second Table design proper joining and if i return a single row for Invoice number 13 all the other multiple lines table records should display..
    – M.I.M.F
    Aug 24, 2016 at 4:35
  • 1
    I still don't follow you. Are you trying to report a record per invoice or per payment? You said, if the invoice only has one payment, then include the details. Does that mean we should only display those invoices that have only 1 payment? If not and you want to include the details from all invoices, you need a way to aggregate the values from those payments. That is unless you just want to show a line for each payment and have the details from the invoice level duplicate. Once you know that, you know where you need to apply the GROUP BY clause. Aug 24, 2016 at 15:46

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.