mysql - Get all users in a the top-level group (all users are members of that group, but exclude users who are also members of other, children groups as well) -

theoretically have 2 tables: users, groups third table user_group joins them (tells users in group).

here's how groups structured (tree-like):

1 root

1.1 child1

1.2 child2

all users assigned @ least root group. additionally, may (but not necessary) assigned different group children of root group (e.g. child1 or child2).

given group id, i'd retrieve users of group id. funny part following scenario: if root group's id provided, should retrieve users present in root group - should not member of other child group.

there another, quite annoying way:

select * (   select iduser, idgroup   usergroup   group iduser   having count(idgroup) < 2 ) idgroup = <group's id you're looking for> 

i said annoying because don't double selects.

old answer below:

with structure this:

users      usergroup      groups ------     -----------    ------ iduser     idusergroup    idgroup            iduser            idgroup 

you try this:

select distinct iduser usergroup ug ug.idgroup=1 group ug.idgroup 

where 1, in where clause idgroup want search for:

 ug.idgroup=<idgroup>