Creating a view and getting duplicate transactions

Question

I have the following view:

SELECT     t.CompanyCode, t.FlatFileName, t.RecordNumber, t.Status, t.Branch, t.SalesMonthYear, t.SalesCashOrLimboCode, t.PageNumber, t.Driver, t.Truck, 
                      t.CommonProductCode, t.CashCharge, t.SpecialFunction, t.ProductCode, t.UnitOfEntry, t.TransactionBranch, t.AccountNumber, t.ReferenceNumber, t.TransactionDate, 
                      t.PercentFullOrWhenDueCode, t.Quantity, t.DeliveryAccountNumberForTankRentals, t.Amount, t.SalesTax, t.ProductCode2Amount, t.TankSerialNumber, t.SalesMonth, 
                      t.TaxCode, t.SubjectToTax, t.SalesTaxRate, t.ExiseTaxRate, t.FrankHasntDecidedWhatThisFieldIs_ProbablyCentury, t.OriginalSalesMonth, t.AutoCharge, 
                      t.ProductCode2, t.ProductCode2SpecialFunction, t.DiscountCode, t.DiscountRate, t.DiscountDays, t.Century, t.TRFRPD, t.OpenItemAmountPaid, 
                      t.OpenItemReferenceNumber, t.InvoiceFlag, t.InvoiceNumber, t.Time, t.GasQuantity, t.AnodeReading, t.Reading, t.Reason, t.InventorySerialNumber, t.SName, 
                      t.ErrorCode, t.MasterBillingBranchAccountNumber, t.LeaseOnFile, t.PuchaseOrderNumber, t.BillOfLaden, t.Latitude, t.Longitude, t.ContractGasPrice, t.Balance, 
                      t.DeliveryAccount, t.BranchAccountNumber, t.LineNumber, t.DateTimeEntered, t.UserThatEntered, t.WorkstationThatEntered, t.YearMonthDayPosted, 
                      t.HourMinutePosted, t.UserThatPosted, t.WorkstationThatPosted, t.CreditCardNumber, t.CreditCardExperationDate, t.CreditViolation, t.TransactionId, t.CorporationId, 
                      t.IsUpdated, t.IsAdded, t.ClearUpdateAndAdded, p.Description
FROM         Company.Product AS p LEFT OUTER JOIN
                      Company.[Transaction] AS t ON p.ProductCode = t.ProductCode AND p.CorporationId = t.CorporationId

The primary thing I am trying to accomplish is get the description of the product from the product table and add it to the transaction tabled. It will have to be based off the ProductCode.

I should be getting each transaction only once but I'm getting multiple copies of each transaction. I know that the products exists for each of the Corporations, so I assume its due to it seeing those?

From this website, it says to use a LEFT OUTER JOIN:

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

I'm not getting nulls in the descriptions so I have no idea what I am doing wrong.

I assume you have one-to-many relation i.e. for one transaction you have many products. Isn't it? — Hamlet Hakobyan, Dec 26 '12 at 16:25
Are there multiple CorporationID's per ProductCode? That could cause this. A LEFT OUTER JOIN of the above query won't give you NULLs in the Description; it'll give you NULLs in all the fields from the Company table. Is that happening? If you add a DISTINCT to your SELECT statement, does that change the number of rows returned? If so, you've got true duplicates; otherwise, some data is different. — Melanie, Dec 26 '12 at 16:26

score 1 · Accepted Answer · answered Dec 26 '12 at 16:26

1

I would alter the order of your tables:

SELECT  <yourcols>
FROM   company.[transaction] AS t 
LEFT OUTER JOIN company.product AS p 
   ON t.productcode = p.productcode
   AND t.corporationid = p.corporationid

answered Dec 26 '12 at 16:26

Taryn

242,637
56
362
405

score 0 · Answer 2 · answered Dec 26 '12 at 16:24

You probably want to join the transaction on the product because for each product there are more than one transaction. I'd swap the orders in your join and I would also not use on OUTER join because in your scenario, you expect a 1-1 existence for all the transactions you are querying (i.e. there is no transaction for which there is no product)

Creating a view and getting duplicate transactions

2 Answers2