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.

score 117 · Accepted Answer · edited Mar 08 '16 at 22:20

117

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.

edited Mar 08 '16 at 22:20

bobsoap

4,844
7
30
43

answered Jan 31 '11 at 02:21

Nazariy

6,028
5
37
61

I altered the SQL to fit my code: `SELECT g.id, COUNT(m.id_profile) AS members FROM groups_main AS g LEFT JOIN groups_fans AS m USING(id) GROUP BY g.id HAVING members > 4` Results in this MySQL error: "Unknown column 'id' in 'from clause'. – hohner Jan 31 '11 at 02:36
You can use USING statement in only if two columns carry same name, in other cases use ON statement I would update my answer. – Nazariy Jan 31 '11 at 02:39

score 63 · Answer 2 · edited Jun 22 '16 at 20:15

63

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

edited Jun 22 '16 at 20:15

Bryan Ash

4,385
3
41
57

answered Jan 24 '14 at 05:01

Reena Shirale

1,992
1
17
15

Thank you very much.. This helped me to achieve what I was trying to do. – W.M. Oct 31 '16 at 19:20
This helped me in a query I was struggling with. Thank you. – Subrat Pattnaik Dec 22 '16 at 15:55
Thanks for sharing this useful trick. I helps me a lot. I was used as find in set function. – Devsainii Jan 14 '17 at 12:16
Although subqueries are not performance friendly in mysql, this is quick good solution comes handy when performance is not an issue. – Atul Jan 18 '17 at 11:39

score 2 · Answer 3 · answered Nov 10 '17 at 10:08

Maybe I am off the mark here and not understanding the OP but why are you joining tables?

If you have a table with members and this table has a column named "group_id", you can just run a query on the members table to get a count of the members grouped by the group_id.

SELECT group_id, COUNT(*) as membercount 
FROM members 
GROUP BY group_id 
HAVING membercount > 4

This should have the least overhead simply because you are avoiding a join but should still give you what you wanted.

If you want the group details and description etc, then add a join from the members table back to the groups table to retrieve the name would give you the quickest result.

score 1 · Answer 4 · edited Aug 05 '12 at 14:12

1

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.

edited Aug 05 '12 at 14:12

Kristian

21,204
19
101
176

answered Jan 31 '11 at 02:45

bbb

11
1

MySQL joins and COUNT(*) from another table

4 Answers4

Linked