SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table things (
  id serial primary key,
  name text not null
);
create table thing_values (
  thing_id references things(id),
  value text not null,
  unique(value, thing_id)
);
insert into things (id, name) values
  (1, 'ionic'), (2, 'doric');
insert into thing_values (thing_id, value) values
  (1, 'alpha'), (1, 'beta'), (1, 'gamma'), (1, 'theta'),
  (2, 'beta');
select *
from things t
join thing_values tv on t.id = tv.thing_id;


id
name
thing_id
value


1
ionic
1
alpha

1
ionic
1
beta

1
ionic
1
gamma

1
ionic
1
theta

2
doric
2
beta


with thing_matches as (
  select thing_id as id
  from thing_values
  where value in ('beta', 'gamma')
  group by thing_id
  having count(*) = 2
)
select t.name
from things t
join thing_matches tm on tm.id = t.id


name


ionic