Sql-server – Duplicate record arising using join tables

sql server 2014sql-server-2008-r2

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:

Best Answer

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

Related Solutions

Sql-server – Change Tracking across several Tables

My plan for this, is to add a rowversion to each of the source tables, and display the max rowversion from the source tables.

This is making the assumption that rowversion is unique across the entire database and is not incremented in each table (I need to check if that assumption is correct).

SELECT 
 ft.[col1]
,ft.[col2]
,ft.[col3]
,ft.[col4]
,dt.dim_id
,dt.name
, (SELECT MAX(rv) 
   FROM (VALUES (ft.row_ver), (dt.row_ver)) AS value(rv)) AS max_row_ver
FROM [dbo].[myFirstTable] ft
INNER JOIN [dbo].[dim_table1] dt ON ft.[col5_fk] = dt.dim_id

Obviously this only gives you an identifier to use to track changes, you would still need to create a table to track it.

Sql-server – Not retrieving data using Right Join

Yes, the problem seems to be the condition that involves the CashInvoice table. It should be moved from WHERE to ON. Besides that, I'd make a few more changes to the query, to make it more readable:

Refactor using aliases. Makes the queries shorter and easier to read.
Add comments in your code.
Format the queries nicely. No one likes to read code that is 300 characters wide.
Use LEFT JOIN instead of RIGHT JOIN. This is rather a stylistic matter but RIGHT joins are confusing to many. Even worse, if a query combines LEFT and RIGHT joins. I suggest you use one or the other everywhere, with preference to LEFT. This way, the "base" table(s) can be put always first, after the FROM and the "optional" tables in the end. Makes it easier for the future reader to identify which is which, especially when the query has many joins.
Add a statement terminator (;) at the end of every statement. Microsoft recommends that and in future versions non-terminated queries will be throwing errors (some already do).
Adding the WHERE to ON move of the condition,

the query becomes:

SELECT 
    i  . InvoicePaymentId, 
    i  . PaymentDate, 
    i  . InvoicePaymentMethod, 
    i  . IsCashPayment, 
    ci . CashAmount, 
    ci . CashReceived, 
    ci . CashBalance
FROM
        BASE_TInvoice AS i              -- "base" table:      invoice:       i
    LEFT OUTER JOIN 
        BASE_TCashInvoice AS ci         -- "optional" table:  cash invoice:  ci
    ON  
        ci.InvoicePaymentId = i.InvoicePaymentId 
        AND ( ci.IsRecordStatus IS NULL OR ci.IsRecordStatus = 1 ) 
WHERE 
      i.IsRecordStatus = 1  
  AND i.InvoicePaymentId = 1 ;

Best Answer

Related Solutions

Sql-server – Change Tracking across several Tables

Sql-server – Not retrieving data using Right Join

Related Question