CODEWITHSUNDEEP
c#asp.netsql
Chris
Chris

Reputation: 7611

Why is my SQL query returning duplicate results?

The SQL is below - each result is coming up 3 times rather than just once. 

SELECT Consignments.LegacyID, TripDate, CollectionName, DeliveryName, Pallets, Weight, BaseRate, Consignments.FuelSurcharge, AdditionalCharges, BaseRate * Quantity AS 'InvoiceValue', Consignments.Customer, InvoiceNumber,
       CASE
          WHEN child.LegacyID = Consignments.Customer THEN child.LegacyID
          WHEN parent.LegacyID = Consignments.Customer THEN parent.LegacyID
          ELSE this.LegacyID
       END AS 'InvoiceAcc'
FROM SageAccount this
  LEFT JOIN SageAccount parent on parent.LegacyID = this.InvoiceAccount
  LEFT JOIN SageAccount child on this.LegacyID = child.InvoiceAccount
  JOIN Consignments on (Consignments.Customer = this.LegacyID AND this.Customer = 'True')
                    OR (Consignments.Customer = parent.LegacyID AND parent.Customer = 'True')
                    OR (Consignments.Customer = child.LegacyID AND child.Customer = 'True')
WHERE (this.LegacyID = @Customer) AND (TripDate BETWEEN @fromdate AND @todate) AND (InvoiceNumber IS NOT NULL)

The SQL was given to me for another similar query, but this time I have modified it to try to use it for the query I'm doing now, so I'm assuming that I'm doing something stupid.

Many thanks.

Upvotes: 1

Views: 310

Answers (2)

Bonshington
Bonshington

Reputation: 4032

JOIN Consignments on (Consignments.Customer = this.LegacyID AND this.Customer = 'True')
OR (Consignments.Customer = parent.LegacyID AND parent.Customer = 'True')
OR (Consignments.Customer = child.LegacyID AND child.Customer = 'True')

i guess u should use "this.InvoiceAccount" instead of this.LegacyID

Upvotes: 1

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

probably because your left joins have 3 child rows per parent row. do a select * to see where your results acutally differ in the rows that are multiplied.

Upvotes: 3

Related Questions