Duplicate record arising using join tables

Question

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

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:

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):

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:

Answer 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).