Блог

Главная - Вопросы по программированию - SQL join group_concat не возвращает некоторые строки

SQL join group_concat не возвращает некоторые строки

#sql #mariadb #inner-join #group-concat

#sql #mariadb #внутреннее соединение #group-concat

Вопрос:

В моей базе данных у меня есть следующие таблицы:

 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           |
 -------- ----------------

Я хочу объединить все таблицы, чтобы получить что-то вроде этого:

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

Я попытался присоединиться к group_concat следующим образом

 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;

Однако, поскольку group_concat не возвращает пустые строки, я получаю все, кроме черного и белого в строке Chris. Я не знаю, как это сделать, так что кто-нибудь может мне помочь, пожалуйста?

Комментарии:

1. Какую СУБД вы используете?

2. @jarlh я использую MariaDB в командной строке в Ubuntu.

3. На заметку: оставайтесь последовательными в своем именовании. IdPeople это хорошее имя для идентификатора таблицы people. (Однако я бы, вероятно, предпочел использовать people_id или id_people для удобства чтения, независимо от верхнего / нижнего регистра.) Не называйте одно и то же People в ref таблице. Дайте ему то же имя. Тогда почему IdCol ? Почему бы и нет IdColor , потому что это идентификатор цвета в таблице цветов? При более крупной базе данных такое отсутствие соглашения об именовании может стать проблемой.

4. @ThorstenKettner спасибо за этот совет. Мои таблицы на самом деле так не называются, я просто переименовал их, чтобы их было легче понять без контекста.

Ответ №1:

Поскольку ref.code может быть null, необходимо внешнее соединение с таблицей кода : LEFT OUTER JOIN Codes ON ... .

Полный запрос:

 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;
Метки: