Postgres Query to retrieve rows which has all the flag value as false

I have User to Role table with Many to Many relation. I need to retrieve all the roles which are not privileged(false) to any users.

select distinct r.rolename,ur.privileged from pam."role" r join pam.user_role ur on r.id =ur.role_id 
where privileged = false;

Above query retrieves record if it has at-least one row with privileged as true, for example opr4 is privileged false for User3 but it is true for User2. So query result should ignore this record. How can I achieve this usecase . Any help is much appreciated

Answer

Use aggregation and bool_or():

select r.rolename
from pam."role" r join
     pam.user_role ur
     on r.id =ur.role_id 
group by r.rolename
having not bool_or(ur.privileged);

Or, if you prefer:

having count(*) filter (where not ur.privileged) = 0

Advertisement

Answer