Select only if one user role in SQL

Question

SELECT user_id FROM user_accounts 
INNER JOIN roles_users
ON (user_accounts.user_id = roles_users.user_id)
WHERE roles_users.role_id IN (1)

Is what i tried.

What i wish is to select all users user_id, that are only a user (role_id 1). In my application everyone has role_id 1, and then they got a new row with their user_id and role_id with either 2,3,4 if they are a partner,admin,etc.

So how can i select all users user_id that ONLY have 1 row in roles_users, with the role_id 1?

MySQL

check my answer on this post: http://stackoverflow.com/questions/6005240/best-way-to-implement-permissions/6005488#6005488 — dqhendricks, Oct 29 '11 at 18:54

score 2 · Accepted Answer · answered Oct 29 '11 at 18:42

2

SELECT user_accounts.user_id, COUNT(roles_users.user_id) AS cnt
FROM user_accounts
INNER JOIN roles_users ON (user_accounts.user_id = roles_uers.user_id)
GROUP BY user_id
HAVING (cnt = 1)

answered Oct 29 '11 at 18:42

Marc B

356,200
43
426
500

score 0 · Answer 2 · answered Oct 29 '11 at 18:41

You can use GROUP BY with a HAVING clause that limits to results that have only one row:

SELECT user_accounts.user_id FROM user_accounts 
INNER JOIN roles_users
ON (user_accounts.user_id = roles_users.user_id)
WHERE roles_users.role_id = 1
GROUP BY user_accounts.user_id
HAVING COUNT(user_accounts.user_id) = 1

score 0 · Answer 3 · answered Oct 29 '11 at 18:42

How about something like this

SELECT user_id 
FROM roles_users
GROUP BY user_id 
HAVING COUNT(*) = 1 and MIN(role_id) = 1

You could also write it like this:

SELECT user_id 
FROM roles_users RU
WHERE role_id = 1 
AND NOT EXISTS(SELECT * FROM roles_users WHERE role_id <> 1 and user_id = RU.user_id)

Select only if one user role in SQL

3 Answers3