CODEWITHSUNDEEP
sqloracle-database
Carven
Carven

Reputation: 15660

How to change the OR logic to AND for the "in" keyword in SQL?

When I do a query using the in keyword like so:

select * from users_roles where user_grp in ('SUPPORTER', 'MEMBER', 'VIP');

In this SQL, I'm saying that select those users who are in any one of the user group. So this is a OR logic.

But can I change the OR logic to AND so that I'm and selecting records that are in all or the items in the list?

The table structure is simply using a one-to-many relationship, in which one user can be in multiple groups, so there can have multiple rows of the same user in the users_roles table with different user_grp.

So, how do I write my SQL so that I select users who are in all of the user groups?

Upvotes: 0

Views: 61

Answers (1)

San
San

Reputation: 4538

You need to apply group by and then select the data that occurs three times after apllying filter

select user_id -- or user_name, whatever
  from users 
 where user_grp in ('SUPPORTER', 'MEMBER', 'VIP')
 group by user_id
having count(distinct user_grp) = 3;

Upvotes: 1

Related Questions