MySQL: Return all results where all conditions (but no other) are true

Question

There are two tables:

Packages

id     name
1      red
2      blue
3      yellow

Contents

packageid      item           size
1              square         A
1              circle         B
1              triangle       C
2              square         A
2              circle         B
3              square         A

If we query for { item:square, size:A } we only want { packages.id:3 }

If we query for { item:square, size:A } and { item:circle, size:B } we only want { packages.id:2 }

If we query for { item:square, size:A } and { item:circle, size:B } and { item:triangle, size:C } we only want { packages.id:1 }

If there is more than one package that matches exactly, we would like all of them.

This seems to work, but it's not terribly elegant:

SELECT 
p.id,
p.name,
c.item,
c.size

FROM Packages p
LEFT JOIN Contents c ON (
  c.packageid=p.id
)

WHERE (
    ( p.id IN ( SELECT packageid FROM Contents WHERE item='square' AND size='A' ) 
    AND 
    ( p.id IN ( SELECT packageid FROM Contents WHERE item='circle' AND size='B' ) 
)
GROUP BY p.id 
HAVING ( SELECT COUNT(*) FROM Contents WHERE packageid=p.id ) = 2;

Answer 1

With EXISTS, you will get your wanted results

Exist is the fastest way to check if values with the wanted values are there.

an INDEX on poacage_id, item and size will increase the perfomace

SELECT 
    p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND  (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'triangle' AND size = 'C')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 3;

id	name	item	size
1	red	triangle	C
1	red	circle	B
1	red	square	A

SELECT
p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 2;

id	name	item	size
2	blue	circle	B
2	blue	square	A

fiddle