CODEWITHSUNDEEP
phpmysqlsqljoin
Lakshmaji
Lakshmaji

Reputation: 1267

How to get list of members belonging to specific group

I have the following tables

groups table

group_types table

I want to get the all details of group (i.e. group members, group types in a single sql query using joins)

SAMPLE JSON OUTPUT FOR GROUP ID 2

{
group_name : second,
group_type:private,
members_list : john , ben
}

I had written the following sql join but I haven't reached my goal.!!

select uls.name "username", gls.name "group name",gtp.name "group type",gus.status
from user_list uls, group_list gls, group_type gtp, group_users gus
where
uls.user_id=gus.user_id and
gls.group_id=gus.group_id and
gtp.type_id=gls.type_id and
gus.status='active' 
group by gls.name

How can I achieve this ?

Upvotes: 1

Views: 163

Answers (1)

Shadow
Shadow

Reputation: 34231

I would use group_concat() function to get the list of members into a single field. I would also use explicit join and full group by:

select group_concat(uls.name) "username", gls.name "group name",gtp.name "group type",gus.status
from user_list uls
inner join group_list gls
inner join group_type gtp
inner join group_users gus
on
uls.user_id=gus.user_id and
gls.group_id=gus.group_id and
gtp.type_id=gls.type_id
where gus.status='active' 
group by gls.name, gtp.name, gus.status

Upvotes: 1

Related Questions