MySQL - get elements from a link table but ONLY those elements

Question

I have a problem selecting from a link table and filtering out superfluous results.

Publications belong to packages by means of the package_publications table.

For instance, I know the ids of my publications to be 11 and 47. I want to return a package that has ONLY those publications in it.

Now if I do a join and do something like where publications.id IN (11, 47) after the obvious inner joins, this will simply return all packages that have either one of those values in the package_publications link table.

I want to match packages that only have BOTH (or more - it isn't limited to two). Effectively I'm using an OR when I want an AND but I don't know how to approach it SQL-wise.

Answer 1

Your best bet is a correlated subquery. The other alternative is join with distinct / group by but it's not going to perform well, especially on a larger tables.

Subquery:

SELECT * FROM `packages`
 WHERE 2 = (
   SELECT count(*) FROM `package_publications`
    WHERE `packages`.id = `package_publications`.package_id
      AND `package_publications`.publication_id IN (11, 47)
 )

Answer 2

Since the packages that have both publication ids in them will show up twice in the result set you can use that to filter. It's not very efficient, but may solve the problem until you find a better solution.

SELECT * FROM packages WHERE packages.id IN (
  SELECT package_publications.package_id
  WHERE package_publications.publication_id IN (11,47)
  GROUP BY package_publications.package_id
  HAVING COUNT(package_publications.package_id) = 2
)