Oracle 11g Release 2

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

create table table1 (person, qualities) as
select 'Joe', '5,6,7,8,9' from dual union all
select 'Mary', '7,8,10,15,20' from dual union all
select 'Bob', '7,8,9,10,11,12' from dual;
3 rows affected

create table table2 (id, descr) 
as
select 5, 'Nice' from dual union all
select 6, 'Tall' from dual union all
select 7, 'Short' from dual ;
3 rows affected

create table table3 (id, descr) 
as
select 8, 'Angry' from dual union all
select 9, 'Sad' from dual union all
select 10, 'Fun' from dual ;
3 rows affected

create table table4 (id, descr) 
as
select 11, 'Boring' from dual union all
select 12, 'Happy' from dual union all
select 15, 'Cool' from dual union all
select 20, 'Mad' from dual ;
4 rows affected

create or replace view allqualities
as
select 1 as category, id as qid, descr from table2
union
select 2, id, descr from table3
union
select 3, id, descr from table4
;
select * from allqualities order by category, qid ;


CATEGORY
QID
DESCR


1
5
Nice

1
6
Tall

1
7
Short

2
8
Angry

2
9
Sad

2
10
Fun

3
11
Boring

3
12
Happy

3
15
Cool

3
20
Mad


select person
from (
  select unique person, category
  from table1 T1 
    join allqualities A 
      on ',' || replace( T1.qualities, ' ', '' ) || ',' like '%,' || A.qid || ',%'
)
group by person
having count(*) = ( select count( distinct category ) from allqualities )
;


PERSON


Bob

Mary

CATEGORY	QID	DESCR
1	5	Nice
1	6	Tall
1	7	Short
2	8	Angry
2	9	Sad
2	10	Fun
3	11	Boring
3	12	Happy
3	15	Cool
3	20	Mad