MySQL count problem
- by Skuja
I have 3 tables users(id,name),groups(id,name) and users_groups(user_id,group_id). users and groups have many to many relationship, so the third one is for storing users and groups relations. I would like to select all the data from groups with user count in each group. So far I came up with this:
SELECT groups.*, COUNT(users_groups.user_id) AS user_count
FROM groups
LEFT JOIN users_groups ON users_groups.group_id = groups.id
The problem is that query result is not returning any of groups which has no users (users_groups doesnt have any records with group_id of those groups).
How should I create my query to select all the groups and they user count, or user count as 0 if there are no users for that group?
