By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table products (
product_id int,
name varchar(20)
);
✓
insert into products values
(1, 'book'),
(2, 'game'),
(3, 'desk');
✓
create table tags (
product_id int,
tag varchar(20)
);
✓
insert into tags values
(3, 'used'),
(3, 'physical'),
(2, 'used'),
(2, 'physical'),
(1, 'used'),
(1, 'not used');
✓
select t.product_id, max(p.name)
from products p
inner join tags t on t.product_id = p.product_id
inner join (
SELECT value as tag
FROM json_each('["used", "physical"]')
) as s on s.tag = t.tag
group by t.product_id
having count(*) = json_array_length('["used", "physical"]');
| product_id | max(p.name) |
|---|---|
| 2 | game |
| 3 | desk |