Many-to-Many gives no result

Question

at first my Many-to-Many-Relationship is with the following tables:

+----------+      +--------------+     
| users    |      | users_groups |     +--------------+
+----------+      +--------------+     | groups       |
| user_id  |----->| user_id      |     +--------------+
| username |      | group_id     |<----| group_id     |
| realname |      +--------------+     | group_name   |
| password |                           | group_desc   |
+----------+                           +--------------+

Now I want to get a list/table (not a table in the DB!) of all existing groups with all the users of the specific group, something like this:

+---------------+--------------------------+
| group         | members                  |
+---------------+--------------------------+
| Group1        | User1, User2, User3      |
| Group2        | User3, User4, User6      |
| Group3        |                          |
+---------------+--------------------------+

My current MySQL code is:

$all_groups = mysqli_query
            ($db,
            "
            SELECT g.group_name,GROUP_CONCAT(DISTINCT u.realname SEPARATOR ', ') AS users
            FROM groups g
            JOIN users_groups ug1 ON g.group_id=ug1.group_id
            JOIN users u ON ug1.user_id=u.user_id
            GROUP BY g.group_name
            "
            );

My current PHP-Code is:

while($row_all_groups = mysqli_fetch_array($all_groups)) {
    echo '<tr>';
    echo '<td class="td_contentbar">'.$row_all_groups["group_name"].'</td>';
    echo '<td class="td_contentbar">'.$row_all_groups["users"].'</td>';
    echo '</tr>';
}

But the problem is that if a group has no users (in users_groups-table) the group isn't shown in the list/table i have and I have no idea how to solve this.

Replacing JOIN users_groups with LEFT JOIN users_groups did not work.

Answer 1

You simply need a left join:

SELECT g.group_name,
       GROUP_CONCAT(DISTINCT u.realname SEPARATOR ', ') AS users
FROM groups g LEFT JOIN
     users_groups ug1
     ON g.group_id = ug1.group_id LEFT JOIN
     users u ON ug1.user_id = u.user_id
GROUP BY g.group_name;

When using LEFT JOIN, normally you put the table with the rows you want to keep first. Then you use LEFT JOIN for all the subsequent joins.

Also, your data probably has no duplicate user names in a group. The DISTINCT keyword hurts performance and should only be used when needed.