CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
MAW74656
MAW74656

Reputation: 3549

Why is this query doubling the results?

I have the following query which is returning 4 results when the table FI_CurrentReceiptData only has 2 rows. The extra rows are exact duplicates. I can use DISTINCT to fix, but my understanding is that this is bad practice and I would do better to find and correct the error (IS THIS CORRECT?).

Why is this query doubling the results?

All variables are declared elsewhere in a stored procedure and this query does return the needed fields.

SELECT ( (Select Max(ReceiptNumber)
          from   Avanti_InventoryReceipts) + CR.Seq ),
       CR.ItemNumber,
       Convert(char(8), GETDATE(), 112),
       PONum,
       'FL-INV',
       PH.POVendor,
       0,
       0,
       'O',
       CR.QtyOrdered,
       QtyReceivedToday,
       QtyReceivedToday,
       Case @closePO
         When 'N' Then Case
                         When ( QtyOrdered - QtyReceivedToday ) < 0 Then 0
                         Else ( QtyOrdered - QtyReceivedToday )
                       End
         When 'Y' Then 0
         Else 0
       End,
       PD.TransCost * QtyReceivedToday,
       IH.PriceWholeSale,
       IH.CostLast,
       QtyReceivedToday,
       0,
       '',
       PODetailDescription /* , .... More columns...*/

FROM   FI_CurrentReceiptData CR
       LEFT JOIN Avanti_PODetails PD
         ON CR.PONum = PD.PONumber
       LEFT JOIN Avanti_POHeader PH
         ON CR.PONum = PH.PONumber
       LEFT JOIN Avanti_InventoryHeader IH
         ON CR.ItemNumber = IH.ItemNumber

NEW WHERE CLAUSE SOLUTION:

FROM FI_CurrentReceiptData CR
inner JOIN Avanti_PODetails PD ON CR.PONum = PD.PONumber AND CR.ItemNumber = PD.TransItem AND CR.QtyOrdered = PD.TransQty
left JOIN Avanti_POHeader PH ON PD.PONumber = PH.PONumber
left JOIN Avanti_InventoryHeader IH ON CR.ItemNumber = IH.ItemNumber

Upvotes: 0

Views: 1793

Answers (3)

M.R.
M.R.

Reputation: 4837

Usually when you string left joins together, you have to keep joining the 'left' table - its not a rule but its good practice so you don't miss joins. In your string of left joins, it includes 4 tables, and you only joined the first one to all three:


FROM   FI_CurrentReceiptData CR 
       LEFT JOIN Avanti_PODetails PD 
         ON CR.PONum = PD.PONumber 
       LEFT JOIN Avanti_POHeader PH 
         ON CR.PONum = PH.PONumber 
       LEFT JOIN Avanti_InventoryHeader IH 
         ON CR.ItemNumber = IH.ItemNumber

You aren't joining PD to IH to PH, but basically left joining the CR table to all three - so essentially it is doing a cross join between PD and PH and IH (based on whatever fields they are matching on). I assume POheader and POdetails have some fields they match on.... and I assume its a one to many relationship between the tables? (based on the names). So there needs to be some restriction there...

EDIT:

So can you try, (based on the field relationships)


FROM   FI_CurrentReceiptData CR 
       LEFT JOIN Avanti_PODetails PD 
         ON CR.PONum = PD.PONumber 
       LEFT JOIN Avanti_POHeader PH 
         ON CR.PONum = PH.PONumber AND PD.PONumber = PH.PONumber
       LEFT JOIN Avanti_InventoryHeader IH 
         ON CR.ItemNumber = IH.ItemNumber

EDIT 2:

Ok, so by what you are saying, CR has 2 rows, and CR has a 1-to-1 relationship to PH, and PH has a 1-to-many relationship to PD. And CR also has a 1-to-t relationship to IH. In which case, assuming you have referencial integrity, you can change your joins to this:


FROM   FI_CurrentReceiptData CR 
       LEFT JOIN Avanti_POHeader PH ON CR.PONum = PD.PONumber 
       INNER JOIN Avanti_PODetails PD ON PH.PONumber = PD.PONUmber
       LEFT JOIN Avanti_InventoryHeader IH ON CR.ItemNumber = IH.ItemNumber

Lets take a step back - this will restrict the PD and PH rows based on whether they match or not, and won't get nulls even if they don't (which is what left joins do).

But now, if you have 2 rows in CR, and each of the PONumbers have 2 rows in POHeader, and each POheader has more than 1 row in POdetails, then you will definitely have duplicates. There is really no way to avoid this. The reason is this:

CR TABLE


PONum            ItemNumber
---------        ----------
123              ABC
456              DEF

PH TABLE


PONumber     
---------        
123              
456

PD TABLE


PONumber     ItemNumber     
---------    -----------    
123          ABC    
123          ABC1
456          DEF
456          DEF1

...if you join the CR table to PH table, you will get only two rows. If you join PH to PD tables, then you will 4 rows. If you join all 3, then you will also get 4 rows. The nature of the join is across all the table, so when you go from the CR table to PH, it gets a resultset of 2 rows...and then that resultset gets join to PD, and there 4 rows. If you can confirm that your data is as such, then the resultset you are getting is correct.

Upvotes: 3

PaulStock
PaulStock

Reputation: 11283

Since you're doing LEFT JOINS, you will get 1 row returned for all matching records. So one of the tables you are joining to is returning more than 1 row.

Try temporarily changing your query to add the other tables primary key to the column list and you should be able to quickly tell which table is returning more than 1 row.

Upvotes: 1

Klas Lindb&#228;ck
Klas Lindb&#228;ck

Reputation: 33283

One of the joined tables has more than one matching row. I cannot say for certain which one, but if I were you I'd start looking at the Avanti_PODetails table. Does it have more than one matching record for any of the two receipts?

Upvotes: 0

Related Questions