Dusan Dimitrijevic Dusan Dimitrijevic -4 years ago 69
MySQL Question

MySQL query select groups with 5 members or higher

I have groups and members in and i need to check now each group if it has 5 members or higher. So everything i need is to get only one row or none.
This is what i have tried so far:

SELECT groups.id, COUNT( user_groups.user_id ) as group_user_count
FROM groups
INNER JOIN user_groups ON user_groups.group_id = groups.id
WHERE groups.id = $group_id AND IFNULL(group_user_count, 0) >= 5;


But this is giving me unknown column for group_user_count

EDIT

Table structure design:

groups:
- id (PK)

users:
- id (PK)
- name

user_groups:
- id (PK)
- user_id (FK)
- group_id (FK)


But i think Forward's answer will solve my problem.

Answer Source

In where clause, you should not use column alias, try to use having:

SELECT groups.id, COUNT( user_groups.user_id ) as group_user_count
FROM groups
INNER JOIN user_groups ON user_groups.group_id = groups.id
WHERE groups.id = $group_id 
HAVING IFNULL(group_user_count, 0) >= 5;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download