I have the following simple table

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!

CodePudding user response：

CREATE temp TABLE test102 (
    col1 text,
    col2 text,
    col3 text
);

INSERT INTO test102
    VALUES ('U1', 'L1', 'X'),
    ('U1', 'L5', 'X'),
    ('U2', 'L2', 'X'),
    ('U3', 'L2', 'X'),
    ('U4', 'L4', 'X'),
    ('U4', 'L6', 'X'),
    ('U5', 'L7', 'X'),
    ('U2', 'L2', 'X');

(
    SELECT
        COL1
    FROM
        test102 t
    WHERE
        t.COL3 = 'X'
        AND t.COL2 IN ('L1', 'L2', 'L3', 'L4'))
EXCEPT ALL (
    SELECT
        COL1
    FROM
        test102 t
    WHERE
        t.COL3 = 'X'
        AND t.COL2 NOT IN ('L1', 'L2', 'L3', 'L4'));

except all return duplicates, except distinct remove duplicates.

CodePudding user response：

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;