[SOLVED] MySQL - Missing rows due to NULL nested SELECT and JOIN

MySQL - Missing rows due to NULL nested SELECT and JOIN

I have four tables at the moment, users, user_groups, user_group_memberships and user_group_permissions. user_group_memberships is used to link a users.id to a user_groups.user_id and user_group_permissions is used to link members of a group to a list of permissions/rights.

I want to run a single query that gets an array of all groups from user_groups, and in the same query get the number of members in each group from user_group_memberships, then I would like to get the name of the user policy from user_group_permissions.

The query 'works' if every user_groups has members in user_group_memberhips and if every user_groups record has a policy set in user_group_permissions., but this will not return any groups that do not yet have member or a user policy assigned. Am I misunderstanding the handling if NULL or my JOIN?

SELECT  ug.*, 
    (SELECT count(*) FROM user_group_memberships WHERE ug.id = ugm.group_id) AS member_count,
    (SELECT policy_name FROM user_group_permissions WHERE ugp.id = ug.user_policy_id) AS policy_name 
FROM 
    user_groups AS ug
LEFT JOIN 
    user_group_memberships AS ugm ON ug.id = ugm.group_id 
LEFT JOIN 
    user_group_permissions AS ugp ON ug.user_policy_id = ugp.id 
WHERE 
    ug.organisation_id=?


users
+----+-----------------+
| id | username        |
+----+-----------------+
|  1 | Thomas          |
|  2 | Harry           |
+----+-----------------+

user_groups
+----+-----------------+-------------------+------------+
| id | organisation_id | user_permission_id| group_name |
+----+-----------------+-------------------+------------+
|  1 | 123             |  1                | Finance    |
|  2 | 123             |  2                | Support    |
+----+-----------------+-------------------+------------+

user_group_memberships
+----+-----------------+----------+----------+
| id | organisation_id | user_id  | group_id |
+----+-----------------+----------+----------+
|  1 | 123             | 1        | 1        |
|  2 | 123             | 2        | 1        |
+----+-----------------+----------+----------+

user_group_permissions
+----+-----------------+
| id | policy_name     |
+----+-----------------+
|  1 | Finance         |
|  2 | Support         |
+----+-----------------+

Using the example above, I would expect my query to return two rows (one for each group), with member_count = 2 for group 1 (row 1) and member_count = 0 for group 2 (row 2). Currently it only returns a single for group 1 as member_count exists/is not null. It returns no data for group 2 as there are no records for group 2 in group_memberships to satisfy the COUNT().

The same issues occurs when user_groups.user_permissions_id is NULL, it will only return a group record if the group has members and if the group has user_permission_id set.

Solution

Your usage of Correlated subqueries is wrong. Also, to get the count of members, you dont need to use Subquery; you can use Group by with Count().

Try:

SELECT ug.id,  
       ug.organisation_id, 
       ug.group_name, 
       COUNT(ugm.group_id) AS member_count,
       ugp.policy_name 
FROM 
    user_groups AS ug
LEFT JOIN 
    user_group_memberships AS ugm ON ug.id = ugm.group_id 
LEFT JOIN 
    user_group_permissions AS ugp ON ug.user_policy_id = ugp.id   
WHERE 
    ug.organisation_id=? 
GROUP BY 
  ug.id, 
  ug.organisation_id, 
  ug.group_name, 
  ugp.policy_name