goal: I want to filter out users who have only a single specified role assigned.
expected result: should display user id with specified role id
actual result: I am getting user IDs who have more than a single specified role assigned.
error messages: NO error messages
What I have done: I have two table
first one is user_profile and other one is user_profile_role. I am joining these 2 tables to filter out the users who have role id as 10005. some users contains more than single role ids. some users only contain role id 10005.
I want to only list down the users who have role id as 10005. but they shouldnt contains non other roles ids.
below is my user_profile table
| id | enabled |
|---|---|
| 2222 | 1 |
| 3333 | 1 |
| 4444 | 1 |
| 5555 | 1 |
below is my user_profile_role table
| id | role_id |
|---|---|
| 2222 | 10005 |
| 2222 | 10004 |
| 3333 | 10005 |
| 4444 | 10005 |
| 5555 | 10004 |
| 5555 | 10005 |
Im using below SQL query to filter out above I mentioned. But it seems my sql query not outputing desired result.
select distinct u.id,u.enabled,ur.role_id
from USER_PROFILE u right outer join USER_PROFILE_ROLE ur
on u.id=ur.id
where u.enabled=1 AND ur.role_id='10005';
when I run above query I am getting output as below ( which is not I want)
| id | enabled | role_id |
|---|---|---|
| 2222 | 1 | 10005 |
| 3333 | 1 | 10005 |
| 4444 | 1 | 10005 |
| 5555 | 1 | 10005 |
below is the output I want
| id | enabled | role_id |
|---|---|---|
| 3333 | 1 | 10005 |
| 4444 | 1 | 10005 |
If anyone can point out what I am missing in my sql query and point me a direction , I would be much appreciated.
You can do:
select id, enabled
from (
select
u.id,
u.enabled,
sum(case when r.role_id = 10005 then 1 else 0 end) as cnt_searched,
count(*) as cnt
from user_profile u
join user_profile_role r on r.id = u.id
where u.enabled = 1
group by u.id, e.enabled
) x
where cnt_searched = cnt