Geek asks and answers

SQL query: see Relationship between an array of users and groups

I have a small application in which users can log in and do what they do there. The database structure for users has nothing in common. There are three tables:

users group user_group_relation

Now, how can I get a list of all groups along with membership status for an array of users?

Let me clarify this with an example.

David is a member of 'users , ' the administrators , "economy"
Eric is a member of the "users" administrator
Richard is a member of the "Administrators"
Lisa is a member of the "administrators , " economy "

Here is the result I would like to get from sql query

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

users   ......................  someAre  
Administrators..........        yes  
Economy ..................      someAre  
Sales   ....................... no
+3
source share
3 answers
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
0
source 
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
+1
source

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
0
source

More articles:


All Articles