CODEWITHSUNDEEP
sql
1.618
1.618

Reputation: 227

How do I exclude a user with a specific criteria where every row has the criteria for that user id?

So I have a list of users who every time they have a feature created, a new row with their ID will be created. I would like to exclude all users that have feature B enabled.

USER   FEATURE
100    A
100    B
200    A
200    C
200    D

I can't do select USER from TABLE where FEATURE =! 'B' because that will still show USER 100. Any other solution?

Upvotes: 0

Views: 66

Answers (2)

Shiwangini
Shiwangini

Reputation: 836

with cte as (select user from table where feature ='B')
select user from table where user not in(select distinct user from cte);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Assuming all users are in this table, you can use aggregation:

select user
from t
group by user
having sum(case when feature = 'B' then 1 else 0 end) = 0;

If users are actually in another table, I would suggest:

select u.*
from users u
where not exists (select 1
                  from user_features uf
                  where uf.user = u.user and uf.feature = 'B'
                 );

Upvotes: 3

Related Questions