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

Question

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:

users

groups

user_group_relation

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', 'economy'

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

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

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

Answer 1

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