I have a straightforward many-many arrangement:
scans
| id |
|---|
| 1 |
| 2 |
products
| id |
|---|
| 1 |
| 2 |
| 3 |
product_scan:
| 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?
