In a large project (collection of solved problems in …) I have a data organized like this (a typical M:N relation):
records | record_id, other_info
tag_map | map_id, record_id, tag_id
tags | tag_id, tag_text, other info
(record_id,tag_id) tuple is unique in the
tag_map table, we could use a combination of join and anti-join operations.
SELECT r.record_id FROM records r JOIN tag_map t1 ON t1.record_id = r.record_id AND t1.tag_id = 1 JOIN tag_map t4 ON t4.record_id = r.record_id AND t4.tag_id = 4 LEFT JOIN tag_map t3 ON t3.record_id = r.record_id AND t3.tag_id = 3 WHERE t3.record_id IS NULL
Again, this is based on the existence of a uniqueness constraint on
tag_map table. Otherwise, this has the potential to return "duplicate" rows.
There are other query patterns that can return equivalent results. For example, we could use a
NOT EXISTS (correlated subquery) in place of the anti-join.