Get groups (only allowed) for a user using a single mysql query

Question

I recently ran into one interesting problem. Add some tables: User, Group and their table Join UserGroup. The following is a dummy view of tables

User

user_id | user_name 
  1     |  a
  2     |  b
  3     |  c

Group

group_id | group_code
  1      |  G1
  2      |  G2
  3      |  G3

user_group

userid  | groupid | exclusion
  1     |   1     |  0
  1     |   2     |  0
  2     |   1     |  1 

Well, the problem is getting a list of groups for the user that he has access to. General assumption of user access in a group:

• If the user does not have an entry in the User_group connection table, the user has access to all groups (i.e. G1, G2 and G3)
• If the user has an entry in User_Group with exception 0, then this user has access only in this group (user 1 has access to G1 and g2)
• User_Group 1, , ( 2 g1 G2 G3)

, 1 3, 2.

SELECT g.id, g.code, ug.user_groups_id, ug.exclusion FROM group g
LEFT JOIN user_group ug ON ug.group_id = g.id 
     AND ug.user_id = 1 -- works for user 2 and 3 but fails for user 1         
WHERE (ug.exclusion = 0 OR ug.exclusion IS NULL) 

, , ,

Answer 1

.

, ( cross join). left join, . :

• 1 , , .
• 2 - , .
• 3 - , .

:

select u.userid,
       (case when max(ug.exclusion) is null  -- all groups
             then group_concat(g.groupcode)
             when max(ug.exclusion) = 0   -- only those included
             then group_concat(case when ug.groupid is not null then g.groupcode end)
             when max(ug.exclusion) = 1   -- exclude these
             then group_concat(case when ug.groupid is null then g.groupcode end)
        end)
from users u cross join
     groups g left join
     user_groups ug
     on u.userid = ug.userid and g.groupid = ug.groupid 
group by u.userid;

, - , , user_groups. user, user_groups. , .