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

(simply adding where role_id=2 is incorrect).

Current output:

user_id  role_id
1        2
1        other
1        3
2        2
3        0

Expected output:

user_id  role_id
2        2

2 answers

  • answered 2018-03-13 21:28 Gordon Linoff

    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);

    If role_id might be NULL, you can adjust this to:

    having min(role_id) = 2 and min(role_id) = max(role_id) and count(role_id) = count(*)

  • answered 2018-03-13 22:03 Daniele Murer

    Try this, it works well with NULLs also:

    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.