CODEWITHSUNDEEP
mysqlsql
torikraju
torikraju

Reputation: 7

query for finding specific data using mysql

Suppose I have a table which contain id(primary key), user-id (foreign key ) and roles( having some roles like USER, ADMIN, SYSTEMADMIN). An user may have multiple roles. I want to find those user only having role USER but not other privileges. I give a snapshot of a similar table. How can I do it.

Image of the table

Upvotes: 0

Views: 28

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a subselect for exclude the userid different from user

select distinct userid from my_table 
where  userid is not in ( select userid from my_table where roles != 'USER')
and roles ='USER';

or you can use an having for count(distinct roles)

select userid from my_table
where roles = 'USER' 
having count( distinct roles) = 1
group by userid

Upvotes: 1

Related Questions