By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE item (
identifier VARCHAR(31),
status VARCHAR(31),
name VARCHAR(31)
);
INSERT INTO item
(identifier, status, name)
VALUES
('fruit', 'yes', 'cherry'),
('fruit', 'yes', 'apple'),
('vegetable', 'yes', 'tomato'),
('vegetable', 'yes', 'lettuce'),
('vegetable', 'no', 'ham'),
('tool', 'no', 'saucepan'),
('tool', 'no', 'knife');
7 rows affected
SELECT item.*
FROM item
WHERE identifier IN (SELECT identifier FROM item WHERE status = 'yes')
AND identifier NOT IN (SELECT identifier FROM item WHERE status = 'no')
| identifier | status | name |
|---|---|---|
| fruit | yes | cherry |
| fruit | yes | apple |