Filter the one-to-many query, requiring all matching criteria

Question

Present the following tables:

create tables (id int, name text, ...);

create a thingsinboxes table (id int, box_id int, subject enum ('apple,' banana ',' orange ');

And the tables look like this:

Boxes:
id | name
1 | orangesOnly
2 | orangesOnly2
3 | orangesBananas
4 | misc

thingsinboxes:
id | box_id | thing
1 | 1 | orange
2 | 1 | orange
3 | 2 | orange
4 | 3 | orange
5 | 3 | banana
6 | 4 | orange
7 | 4 | apple
8 | 4 | banana

How to choose boxes containing at least one orange and nothing that is orange?

How is this scale if I have several hundred thousand boxes and maybe a million things in boxes?

SQL, , script.

postgres, mysql, , , , , mysql ( 6, ).

Answer 1

SELECT b.*
FROM boxes b JOIN thingsinboxes t ON (b.id = t.box_id)
GROUP BY b.id
HAVING COUNT(DISTINCT t.thing) = 1 AND SUM(t.thing = 'orange') > 0;

---

, GROUP BY:

SELECT DISTINCT b.*
FROM boxes b
  JOIN thingsinboxes t1 
    ON (b.id = t1.box_id AND t1.thing = 'orange')
  LEFT OUTER JOIN thingsinboxes t2 
    ON (b.id = t2.box_id AND t2.thing != 'orange')
WHERE t2.box_id IS NULL;

, , , .

Answer 2

, , , , thing:

SELECT b.*
FROM boxes b JOIN thingsinboxes t1 ON (b.id = t1.box_id)
WHERE t1.thing = 'orange'
AND NOT EXISTS (
    SELECT 1
    FROM thingsinboxes t2
    WHERE t2.box_id = b.id
    AND t2.thing <> 'orange'
)
GROUP BY t1.box_id

WHERE NOT EXISTS , , .