title: LEFT JOIN in MySQL across multiple tables with NULL values
body: <p>I have the following table structure with data</p>
  
  <p><code>TABLE: USER
   USER ID | USER NAME
   1      | Joe
   2      | Mary</code></p>
  
  <p><code>TABLE : USER GROUP
  USER ID | GROUP ID
   1      |  1
   1      |  2</code></p>
  
  <p><code>TABLE : GROUP 
  GROUP ID | GROUP NAME
   1       | Company 1
   2       | Company 2</code></p>
  
  <p><code>TABLE : ROLE
   ROLE ID | ROLE NAME
     1     | Administrator
     2     | Users</code></p>
  
  <p><code>TABLE : USER ROLE
   USER ID | ROLE ID
   1       |  1
   2       |  1</code></p>
  
  <p>As you can see user #2 does not belong to any group. Roles &amp; Groups are optional forcing me to left joint but when I run a query as below</p>
  
  <pre><code>`SELECT a.user_id,
          a.user_name
          GROUP_CONCAT(r.role_name) AS role_names,
          GROUP_CONCAT(g.group_name) AS group_names
   FROM  user a
    LEFT JOIN role_map m ON a.user_id = m.user_id
       INNER JOIN role r ON m.role_id = r.role_id
    LEFT JOIN user_group s ON a.user_id = s.user_id
       INNER JOIN group g ON s.group_id = g.group_id
   GROUP BY a.user_id`
  </code></pre>
  
  <p>I get a cartesian product in the <code>role_names</code> column - the result looks like this</p>
  
  <p><code>Joe | Administrators, Administrators | Company 1, Company 2</code></p>
  
  <p>What am I doing wrong?</p>