Help with duplicates in output

Question

I'm in need of some help with getting my duplicates from showing more than once in my output.

SELECT 
  accountNumber AS 'Member Number',  
  OD.orderdetails AS 'iNum', 
  FirstName AS 'First Name', 
  LastName AS 'Last Name', 
  HGP.email AS 'Email', 
  points AS 'Points -->', 
  '$' + CONVERT(varchar(50),(CONVERT(int,Points) * .1)) AS '<-- Amount', 
  CountryCode AS 'Country', 
  CONVERT(VARCHAR(10), issued, 101) AS 'Order Date', 
  CONVERT(VARCHAR(10), cs.RedeemedDate, 101) AS 'R Date' 
FROM tblHGP HGP, OrderDetails OD, tblInvoices i
JOIN tblCS cs ON i.InvoiceNumber = cs.InvoiceNumber
JOIN tblECI ac ON i.InvoiceNumber = ac.InvoiceNumber 
  AND cs.Sold = ac.ECIID
WHERE 
  i.InvoiceNumber = HGP.invoiceNumber 
  AND HGP.issued BETWEEN '2010-01-01' AND '2010-09-01' 
  AND HGP.invoiceNumber = OD.orderdetails
ORDER BY 
  HGP.issued

Like i said above, i am getting duplicates on some of the users. I've tried the distinct but doesn't seem to work.

Any help would be great! :o)

David

Answer 1

More details. From the query it looks like you would get a double whenever an invoice has more than one order detail (more than one item ordered). Thislooks like by design. Naturally dsictinct would not work because not all fields would be identical.

Basically if a user gets invoiced for 2 items in one invoice, he will appear (as will the invoice) twice.

Answer 2

It looks to me like you are only using the join to OrderDetails for this:

OD.orderdetails AS 'iNum',

As shown in your where clause, you can get this same value from HGP:

AND HGP.invoiceNumber = OD.orderdetails

thus you can eliminate the entire join to OD, and eliminate your duplication due to multiple rows in the details table for an invoice. So, rewritten it should look like this:

  SELECT 
  accountNumber AS 'Member Number',  
  HGP.invoiceNumber AS 'iNum', 
  FirstName AS 'First Name', 
  LastName AS 'Last Name', 
  HGP.email AS 'Email', 
  points AS 'Points -->', 
  '$' + CONVERT(varchar(50),(CONVERT(int,Points) * .1)) AS '<-- Amount', 
  CountryCode AS 'Country', 
  CONVERT(VARCHAR(10), issued, 101) AS 'Order Date', 
  CONVERT(VARCHAR(10), cs.RedeemedDate, 101) AS 'R Date' 
FROM tblHGP HGP, tblInvoices i
JOIN tblCS cs ON i.InvoiceNumber = cs.InvoiceNumber
JOIN tblECI ac ON i.InvoiceNumber = ac.InvoiceNumber 
  AND cs.Sold = ac.ECIID
WHERE 
  i.InvoiceNumber = HGP.invoiceNumber 
  AND HGP.issued BETWEEN '2010-01-01' AND '2010-09-01' 
ORDER BY 
  HGP.issued

I can't tell for sure if the non-table qualified column names (i.e. issued, Points, CountryCode, FirstName, LastName) are coming from OrderDetails or not, though they don't look like they would.