php - How to do a SUM subquery within a left join query? -

so have data:

table 'group_info':

identifier      name 123             group 124             group b 

table: 'group_participants':

id              group_id       privilege 1               123            admin 2               123            user 3               124            admin 

i want number of people 'user' or 'admin' , number of people 'admin', along group identifier , name, groups user id 1 member of.

e.g. in case above return 123, 'group a', 2 users , 1 admin.

i'm trying in single query can't quite final part. have far:

select group_info.identifier, group_info.name `group_info` left join group_participants on group_participants.group_id = group_info.identifier group_participants.user_id = 1 

i'm aware can done in 2 queries, i'd rather @ once. ideas how format subquery achieve this?

your question not clear on "user or above" means. answer question use conditional aggregation along having clause:

select gi.identifier, gi.name,        sum(gp.priviledge in ('user', 'admin')) numuseroradmin,        sum(gp.priviledge = 'admin') numadmins group_info gi left join      group_participants gp      on gp.group_id = gi.identifier group gi.identifier, gi.name having sum(gp.user_id = 1) > 0