SQL-query: See relationship between array of users and groups

I have a small application where users can login and do whatever they do in there. The database structure regarding users is nothing fancy. There are three tables:

Now, How can I get a list of all the groups together with membership status for an array of users?

Let me clarify this by an example.

David is member of 'users', 'administrators', 'economy'

Erik is member of 'users', administrators'

Richard is member of 'administrators'

Lisa is member of 'administrators', 'econom开发者_运维知识库y'

Here is the result I would want from an sql-query

GroupName.......................isEveryoneAMember

users   ......................  someAre  
Administrators..........        yes  
Economy ..................      someAre  
Sales   ....................... no

SELECT  g.name,
        CASE
        WHEN mcount = 0 THEN
                'none'
        WHEN mcount = ucount THEN
                'all'
        ELSE
                'some'
        END AS isEveryOneAMember
FROM    (
        SELECT  COUNT(*) AS ucount
        FROM    users
        ) u
CROSS JOIN
        (
        SELECT  group_id,
                COUNT(*) AS mcount
        FROM    user_group_relation ug
        GROUP BY
                group_id
        ) ug
JOIN    groups g
ON      g.id = ug.group_id

select g.GroupName,
    case
        when count(ug.userId) = (select count(*) from users) then 'yes'
        when count(ug.userId) = 0 then 'no'
        else 'someAre'
    end as HowMany
from Groups g
join UserGroups ug on g.id = ug.groupId
group by g.groupName

Assuming there are no duplicate group names:

WITH rollcall AS (
  SELECT
    g.name,
    ug.user_id
  FROM groups g
    CROSS JOIN users u
    LEFT JOIN user_group_relation ug ON g.id = ug.group_id AND u.id = ug.user_id
)
SELECT
  GroupName = name,
  isEveryoneAMember = CASE COUNT(user_ud)
    WHEN 0        THEN 'No'
    WHEN COUNT(*) THEN 'Yes'
    ELSE 'someAre'
  END
FROM rollcall
GROUP BY name