mysql - Flag for users without any unlocked groups -

so have convoluted user system i'm dealing , i'm trying find out groups user part of, , whether or not of groups in locked state.

here's basics of schema i'm using.

users

+--------+ | userid | +--------+ | 1      | +--------+ | 2      | +--------+ 

user_groups

+--------+---------+ | userid | groupid | +--------+---------+ | 1      | 100     | +--------+---------+ | 2      | 200     | +--------+---------+ | 2      | 300     | +--------+---------+ 

groups

+---------+---------+ | groupid | locked  | +---------+---------+ | 100     | 1       | +---------+---------+ | 200     | 1       | +---------+---------+ | 300     | 0       | +---------+---------+ 

so given example, user 2 belongs groups 200, , 300.

group 200 locked, while group 300 not.

what i'm looking list of users, groups (separated comma), , whether or not of groups locked.

+--------+----------+--------+ | userid | groupids | locked | +--------+----------+--------+ | 1      | 100      | 1      | +--------+----------+--------+ | 2      | 200, 300 | 0      | +--------+----------+--------+ 

the query wrote selects group.locked tells me status of first group selected join, right? i'm not confident i'm using grouping correctly.

select users.userid, group_concat(groups.groupid) groupids, group.locked users left join user_groups     on user_groups.userid = users.userid left join groups     on groups.groupid = user_groups.groupid group groups.groupid 

you use min - if 1 0 (unlocked) exists, result 0, , if of them 1 (locked), result 1:

select users.userid,        group_concat(groups.groupid) groupids,        min(group.locked) users left join user_groups     on user_groups.userid = users.userid left join groups     on groups.groupid = user_groups.groupid group users.userid 

(note grouping should done userid , not gorupid)