query for finding specific data using mysql

Question

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

ScaisEdge · Accepted Answer · 2017-02-01 19:38:26Z

1

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

answered Feb 1, 2017 at 19:38

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

query for finding specific data using mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related