MySQL joins and COUNT(*) from another table

Question

I have two tables: groups and group_members.

The groups table contains all the information for each group, such as its ID, title, description, etc.

In the group_members table, it lists all the members who are apart of each group like this:

group_id | user_id
1 | 100
2 | 23
2 | 100
9 | 601

Basically, I want to list THREE groups on a page, and I only want to list groups which have MORE than four members. Inside the <?php while ?> loop, I then want to four members who are apart of that group. I'm having no trouble listing the groups, and listing the members in another internal loop, I just cannot refine the groups so that ONLY those with more than 4 members show.

Does anybody know how to do this? I'm sure it's with MySQL joins.

Answer 1

MySQL use HAVING statement for this tasks.

Your query would look like this:

SELECT g.group_id, COUNT(m.member_id) AS members
FROM groups AS g
LEFT JOIN group_members AS m USING(group_id)
GROUP BY g.group_id
HAVING members > 4

example when references have different names

SELECT g.id, COUNT(m.member_id) AS members
FROM groups AS g
LEFT JOIN group_members AS m ON g.id = m.group_id
GROUP BY g.id
HAVING members > 4

Also, make sure that you set indexes inside your database schema for keys you are using in JOINS as it can affect your site performance.