Reputation: 2132
I'm creating a social mobile-application which has groups and groups can have user. I'm trying to write a query in order to get all the groups along with number of users in it.
Note: Group can have 0 users as well.
Even if group has zero user, I still need to get its information. How should I do that? I tried:
Select *, count(ug.group_id) from groups g
left join images i ON(g.group_image_id = i.image_id)
left join location l ON(g.group_location_id = l.location_id)
.
.
left join user_group ug on(ug.gorup_id = g.group_id)
group by ug.group_id;
Now, this query does not give me group that has zero user(s). How can change it so it shows all group even if group has 0 users.
Upvotes: 0
Views: 256
Reputation: 107826
Using SELECT * and GROUP BY is a MySQL abomination. In your case, it doesn't look like it hurts since image
and location
appear to have a 0:1 relation ship with group
. It's just very bad practice.
Here's an ANSI compliant way to write your query. If you want the full result set from JOINing a few tables, plus the count, then add only the count portion as an expression.
Select *, (select count(ug.group_id)
from user_group ug
where ug.gorup_id = g.group_id) GroupUserCount
from groups g
left join images i ON(g.group_image_id = i.image_id)
left join location l ON(g.group_location_id = l.location_id)
.
.
Upvotes: 1
Reputation: 696
Your are grouping by a column of your left join, this means that your are grouping by null when a group have no users.
You need to group by id of the groups table and the count(*) will count the number of joined rows.
Then add the empty groups using a left join and where join row is null
Select g.group_id as groupId, count(*) as nb
from groups g
join user_group ug on (ug.group_id = g.group_id)
group by g.group_id
UNION ALL
Select g.group_id as groupId, 0 as nb
from groups g
left join user_group ug on (ug.group_id = g.group_id)
where ug.group_id is null
group by g.group_id
[EDIT] One query count looking for null join in the select statement and setting nb to 0 if found
Select g.group_id as groupId, IF(ug.group_id is null, 0,count(*)) as nb
from groups g
left join user_group ug on (ug.group_id = g.group_id)
group by g.group_id
Upvotes: 0
Reputation: 717
I believe you are looking for something like:
Select g.group_id,coalesce(users ,0)
from groups g
left join (select ug.gorup_id,count(*) users
from user_group ug
group by ug.gorup_id) s
on s.group_id = g.group_id
If you only need to know the group and number of users, the other table in the example are not necessary.
Upvotes: 0