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  lt  php while   gt  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

User · Accepted Answer

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.

User · Answer

SELECT DISTINCT groups id           SELECT COUNT    FROM group members         WHERE member id   groups id  AS memberCount FROM groups

User · Answer

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

User · Answer

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  gt  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

[mysql] MySQL joins and COUNT(*) from another table

Examples related to mysql

Examples related to count

Examples related to group-by

Examples related to left-join

Examples related to having