SQL join + group_concat not returning some rows

Question

In my database, i have the following tables:

People
+-----------+------------+
| IdPeople  | Name       |
+-----------+------------+
|         1 | James      |
|         2 | Chris      |
+-----------+------------+

ref
+---------+-------------+------+
| People  | Color       | Code |
+---------+-------------+------+
|       1 |           2 |    1 |
|       1 |           1 |    2 |
|       1 |           6 |    3 |
|       2 |           1 |    1 |
|       2 |           6 |    4 |
|       2 |           4 | NULL |
|       2 |           5 | NULL |
+---------+-------------+------+

Colors
+--------+--------------------+
| IdCol  | Color              |
+--------+--------------------+
|      1 | Blue               |
|      2 | Green              |
|      3 | Yellow             |
|      4 | Red                |
|      5 | Black              |
|      6 | White              |
+--------+--------------------+

Codes
+--------+----------------+
| IdCode | Code           |
+--------+----------------+
|      1 | C++            |
|      2 | JavaScript     |
|      3 | Python         |
|      4 | HTML           |
+--------+----------------+

I want to join all the tables to get something like this:

+------------+---------------------------+------------------------------------+
| Name       | Color                     | Code                               |
+------------+---------------------------+------------------------------------+
| Chris      | Blue, White, Red, Black   | C++, HTML                          |
| James      | Green, Blue, White        | C++, JavaScript, Python            |
+------------+---------------------------+------------------------------------+

I tried to join with group_concat like this

SELECT People.Name,
       group_concat(Colors.Color separator ", ") AS "Color",
       group_concat(Codes.Code separator ", ") AS "Code"
FROM People
INNER JOIN ref ON People.IdPeople=ref.People
INNER JOIN Colors ON ref.Color=Colors.IdCol
INNER JOIN Codes ON ref.Code=Codes.Code
GROUP BY Name;

However, since group_concat doesn't return empty lines, i get everything except Black and White in the Chris line. I don't know how to do this, so can someone help me please?

Answer 1

As ref.code can be null, you must outer join the code table: LEFT OUTER JOIN Codes ON ....

The full query:

SELECT People.Name,
       group_concat(Colors.Color separator ", ") AS "Color",
       group_concat(Codes.Code separator ", ") AS "Code"
FROM People
INNER JOIN REF ON People.IdPeople=ref.People
INNER JOIN Colors ON ref.Color=Colors.IdCol
LEFT JOIN Codes ON ref.Code=Codes.Code
GROUP BY Name;