I have a straightforward many-many arrangement:






scan_id product_id
1 1
1 2
2 1
2 2
2 3

When I'm about to create a new scan entry with a proposed set of products, I want to know if there is already a scan record linked to an identical set of products, to avoid duplicates. So if I have a new scan that wants to link to products 1 and 2, it needs to match scan 1, but not scan 2 . Similarly a new scan with products 1, 2, and 3 would match scan 2, but not scan 1, and a scan with products 2 and 3 would not match either of the existing scans (i.e. not a duplicate). This list of possible products linked to a scan is arbitrary and of variable length and random order, so I can't use hard-coded values in the query.

I've been trying all kinds of weird queries involving subqueries and having clauses, doing things like finding all related record that match the provided IDs and counting the matches, but I can't seem to figure out how to get it to exclude records that are a superset of the requested list. I've now reached a point where I'm generating subqueries in a loop, and it's not pretty, and still doesn't work. I have found some answers like this, which is the stuff of nightmares...

Is there a simple way to do this search?

  • Many-to-many makes the table more efficient and forces uniqueness.
    – Rick James
    Apr 27 at 14:50
  • That's not what I asked; I'm looking for searching on these IDs, not preventing their existence. I already have a unique (primary) key on the join table to prevent the same product being attached to a scan more than once, but that has no bearing on this problem.
    – Synchro
    Apr 28 at 10:02


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.