join where row is distinct
I have a table where user_id is related to the role a user has, so not necessarily 1:1 user to role, because he can have multiple roles.
I am looking to only select the users which have role_id = 2 and not any other role. Can you assist?
select user_id, role_id from users u join roles r on u.user_id=r.user_id
where role_id=2 is incorrect).
user_id role_id 1 2 1 other 1 3 2 2 3 0
user_id role_id 2 2
Here is one method using aggregation:
select user_id, 2 from roles r group by user_id having min(role_id) = 2 and min(role_id) = max(role_id);
NULL, you can adjust this to:
having min(role_id) = 2 and min(role_id) = max(role_id) and count(role_id) = count(*)
Try this, it works well with
SELECT user_id, 2 FROM ( SELECT user_id, COUNT(role_id) FROM roles GROUP BY user_id HAVING MIN(role_id) = 2 AND MAX(role_id) = 2 AND COUNT(*) = COUNT(role_id) ) AS t;
It is huge, but it is the simplest way to do this in PostgreSQL I think.