sql: exclude rows if at least one row within it isn’t in the list

I have the following simple table

COL1	COL2	COL3
U1	L1	X
U1	L5	X
U2	L2	X
U3	L2	X
U4	L4	X
U4	L6	X
U5	L7	X

when I execute the statement

select COL1 
from table t 
where t.COL3= 'X' 
  and t.COL2 in ('L1', 'L2', 'L3', 'L4');

Result:

U1
U2
U3
U4

My desired result is

U2
U3

The U1 and U4 should be filtered out because one of their COL2 contains an element which is not in the list.

Is it possible to achieve it in a SQL statement?

I appreciate any help!

Answer

A full example of how to achieve what you want using a simple aggregate function:

create table test (col1 text, col2 text, col3 text);

insert into test values ('U1', 'L1', 'X');
insert into test values ('U1', 'L5', 'X');
insert into test values ('U2', 'L2', 'X');
insert into test values ('U3', 'L2', 'X');
insert into test values ('U4', 'L4', 'X');
insert into test values ('U4', 'L6', 'X');
insert into test values ('U5', 'L7', 'X');

select
  col1
from (
  select 
    col1,
    -- aggregate all occurences for each value in col1 and see if any is missing, if so this will be false
    bool_and(col2 in ('L1', 'L2', 'L3', 'L4')) as has_value
  from test 
  where col3 = 'X'
  group by col1
) agg
where has_value;