Find duplicate values in SQL search

Question

I'm having a problem finding the right solution for this problem. I want to find duplicate invoices in a list of thousands of invoices and want to make sure were not paying the same invoice twice where the same invoice has been scanned in to our system twice but where the invoice number is not the same but has the same supplier id. I have tried to use COUNT to find all the invoices that have the same supplier id and the same amount but cannot get it to work.

(In the example I want to find the two Johns Bakery invoices)

Example data

Most people here want sample table data _and the expected result_ as formatted text, not as images (or links to them.) Also show us your current query attempt. — jarlh, Aug 24 '21 at 09:23
Does this answer your question? [Finding duplicate values in MySQL](https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) — Cyrus Raoufi, Aug 24 '21 at 11:34

score 0 · Answer 1 · answered Aug 24 '21 at 11:07

You would seem to want:

select ip.*
from invoice_payment ip
where exists (select 1
              from invoice_payment ip2
              where ip2.supplier_id = ip.supplier_id and
                    ip2.name = ip.name and
                    ip2.amount = ip.amount and
                    ip2.invoice_number <> ip.invoice_number
             )
order by supplier_id, name, amount;

Find duplicate values in SQL search

1 Answers1