By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Packages
(`id` int, `name` varchar(6))
;
INSERT INTO Packages
(`id`, `name`)
VALUES
(1, 'red'),
(2, 'blue'),
(3, 'yellow')
;
CREATE TABLE Contents
(`packageid` int, `item` varchar(8), `size` varchar(1))
;
INSERT INTO Contents
(`packageid`, `item`, `size`)
VALUES
(1, 'square', 'A'),
(1, 'circle', 'B'),
(1, 'triangle', 'C'),
(2, 'square', 'A'),
(2, 'circle', 'B'),
(3, 'square', 'A')
;
Records: 3 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
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 |