Cacher is the code snippet organizer for pro developers

# which roles does a user have and not have?
SELECT DISTINCT name, 'NO' as 'HAS?'
FROM portal_role
WHERE name NOT LIKE 'ptm%'
  AND name NOT LIKE 'wcm%'
  AND name NOT IN
    (SELECT DISTINCT pr.name
     FROM portal_user_role pur
     JOIN portal_role pr ON pr. id = pur.role_id
     JOIN portal_user pu ON pu.id = pur.user_id
     WHERE pu.user_name = 'dbar')
UNION
SELECT DISTINCT pr.name, 'YES' as 'HAS?'
FROM portal_user_role pur
JOIN portal_role pr ON pr. id = pur.role_id
JOIN portal_user pu ON pu.id = pur.user_id
WHERE pu.user_name = 'dbar'
ORDER BY 2 DESC, 1 ASC;

# which roles does a user NOT have?
SELECT DISTINCT name
FROM portal_role
WHERE name NOT LIKE 'ptm%'
  AND name NOT LIKE 'wcm%'
  AND name NOT IN
    (SELECT DISTINCT pr.name
     FROM portal_user_role pur
     JOIN portal_role pr ON pr. id = pur.role_id
     JOIN portal_user pu ON pu.id = pur.user_id
     WHERE pu.user_name = 'dbar')
ORDER BY 1;