MySQL - Missing rows due to NULL nested SELECT and JOIN

Question

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.

Please post sample data and expected output. – Madhur Bhaiya Oct 11 '18 at 12:00 — Madhur Bhaiya, Oct 11 '18 at 12:00

score 1 · Accepted Answer · answered Oct 11 '18 at 12:22

1

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

answered Oct 11 '18 at 12:22

Madhur Bhaiya

28,155
10
49
57

1

Thank you very much, I read your answer 10 times and it still made no sense to me (but does work as expected) - so there's definitely a huge gap in my knowledge. I'll read through that link, if you have any other recommended reads please do let me know, as I don't quite understand the requirement/result of the GROUP BY in this scenario. Thanks again! – Arbiter Oct 11 '18 at 13:25
@Arbiter Read the hyperlink on correlated subqueries in my answer. And compare it with your original query. Your query was using it in wrong way. Moreover, you could have solved the same problem using `group by` instead of a subquery (less efficient) methods. `Group By` because you want to use aggregation (count number of rows). – Madhur Bhaiya Oct 11 '18 at 13:30
@Arbiter Also do read this: https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc – Madhur Bhaiya Oct 11 '18 at 13:30

MySQL - Missing rows due to NULL nested SELECT and JOIN

1 Answers1