James:
Are you saying you want to return rows where the invoice number AND the
amount is duplicated in one or more other rows? If so:
SELECT InvoiceNumber, Amount
FROM Invoices
GROUP BY InvoiceNumber, Amount
HAVING COUNT(*) > 1;
or are you saying you want to return rows where the invoice number OR the
amount is duplicated in one or more other rows? If so:
SELECT InvoiceNumber, Amount
FROM Invoices AS I1
WHERE
(SELECT COUNT(*)
FROM Invoices AS I2
WHERE I2.InvoiceNumber = I1.InvoiceNumber) > 1
OR
(SELECT COUNT(*)
FROM Invoices AS I2
WHERE I2.Amount = I1.Amount) > 1;
An alternative to the latter, and probably a more efficient solution, would
be the following, but this depends on the table having a uniquely valued
column such as InvoiceID. If you don't have this then adding an autonumber
column would give you the necessary column:
SELECT InvoiceNumber, Amount
FROM Invoices AS I1
WHERE EXISTS
(SELECT *
FROM Invoices As I2
WHERE (I2.InvoiceNumber = I1.InvoiceNumber
OR I2.Amount = I1.Amount)
AND I2.InvoiceID <> I1.InvoiceID);
Indexing theInvoiceID, InvoiceNumber and Amount columns should help
performance considerably.
Ken Sheridan
Stafford, England
