SQLite 3.39

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