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.

Solution 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.

Solution 2

SELECT DISTINCT groups.id, 
       (SELECT COUNT(*) FROM group_members
        WHERE member_id = groups.id) AS memberCount
FROM groups

Solution 3

Your groups_main table has a key column named id. I believe you can only use the USING syntax for the join if the groups_fans table has a key column with the same name, which it probably does not. So instead, try this:

LEFT JOIN groups_fans AS m ON m.group_id = g.id

Or replace group_id with whatever the appropriate column name is in the groups_fans table.