Есть ли способ вернуть строки с нулевым значением при запросе объединенной таблицы

У меня есть две таблицы: таблица users со столбцами id, name и таблица events со столбцами id, content и userId.

Я пытаюсь запросить таблицу, которая возвращает объединенную информацию из этих двух таблиц со столбцами name и events, где events будет представлять массив полей content, соответствующих пользователю.

Это запрос, который я запускаю:

select 
    name, group_concat(content) as events 
from 
    users 
left join 
    events on id = userId 
group by 
    userId 
order by 
    id

Однако строки со значениями null не возвращаются, кроме одной строки. Что я делаю не так?

Таблица пользователей

[
{
"id": 1,
"name": "Hugo Powlowski"
},
{
"id": 2,
"name": "Jeremy Littel II"
},
{
"id": 3,
"name": "Eleanor King"
},
{
"id": 4,
"name": "Rogelio Jacobson"
},
{
"id": 5,
"name": "Jerald Rowe PhD"
},
{
"id": 6,
"name": "Robyn Tromp"
},
{
"id": 7,
"name": "Norman Zboncak"
},
{
"id": 8,
"name": "Mr. Kristy Orn"
},
{
"id": 9,
"name": "Mrs. Olivia Trantow"
},
{
"id": 10,
"name": "Daniel Lebsack"
}
]

Таблица событий

[
{
"eventId": 3,
"content": "hello",
"userId": 7
},
{
"eventId": 12,
"content": "rulsan berden",
"userId": 1
}
]

Присоединенный стол

[
{
"name": "Hugo Powlowski",
"events": "rulsan berden"
},
{
"name": "Jeremy Littel II",
"events": null
},
{
"name": "Norman Zboncak",
"events": "hello"
}
]

пожалуйста, опубликуйте образец данных - в виде текста

user18098820 04.05.2022 06:58

Вы должны квалифицировать все столбцы с их таблицами, то есть on users.id = events.userId вместо on id = userId, чтобы СУБД знала, о каком идентификаторе вы говорите. Помимо этого запрос выглядит нормально и должен возвращать всех пользователей.

Thorsten Kettner 04.05.2022 07:35

@ThorstenKettner спасибо за ответ. Обратите внимание на ответ Бармара ниже - это правильный ответ

esentai 04.05.2022 09:29

Да, этого я не видел. Я мог бы, если бы все столбцы были квалифицированы с помощью таблиц, как указано. Тогда станет более очевидным, что вы группируете по столбцу таблицы с внешним соединением. Всегда уточняйте все столбцы при работе с более чем одной таблицей.

Thorsten Kettner 04.05.2022 09:31
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
4
43
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Попробуйте использовать вложенный SELECT, это должно вернуть null для users без каких-либо событий:

select 
    u.name, 
    SELECT(
      group_concat(content)  
        FROM 
      events 
        WHERE 
      userId = u.id
    ) as events 
from 
    users u
order by 
    u.id
Ответ принят как подходящий

Вы должны группировать по столбцу в родительской таблице, а не по левой объединенной таблице, чтобы значения никогда не были нулевыми.

Так что измените GROUP BY userid на GROUP BY users.id.

Спасибо, сэр. Я провел 3 дня, изучая sql и пытаясь найти ответ. Я даже не могу представить, как долго и насколько широко мне придется практиковаться, чтобы приблизиться к тому же уровню, что и вы, чтобы иметь возможность просто посмотреть на запрос и увидеть, что не так. Еще раз спасибо!!!

esentai 04.05.2022 09:26

Хорошо подмечено. Это то, что ускользнуло от меня. СУБД, конечно, должна выдавать ошибку, потому что имя пользователя функционально не зависит от идентификатора пользователя с внешним соединением. Ошибка в СУБД, вероятно, пресловутый читерский режим MySQL, где SET sql_mode = 'ONLY_FULL_GROUP_BY' не применяется. @esentai: убедитесь, что вы всегда работаете в режиме ONLY_FULL_GROUP_BY, чтобы СУБД сообщала вам, когда ваш запрос агрегации недействителен.

Thorsten Kettner 04.05.2022 09:28

@ThorstenKettner может, потому что я использовал sqlite, а не mysql или postgres?

esentai 04.05.2022 09:30

@ThorstenKettner, как лучше всего включить режим only_full_group_by? это что-то, что я должен установить в файле конфигурации?

esentai 04.05.2022 09:31

@ThorstenKettner Если users.id является первичным ключом, то users.name должен быть функционально зависимым.

Barmar 04.05.2022 09:33

Ой, извините, я перепутал СУБД. В SQLite такого режима нет. Так что это действительно ошибка, а не просто неправильная настройка СУБД.

Thorsten Kettner 04.05.2022 09:33

@ Бармер: Правильно. Ваш GROUP BY users.id действителен. Оригинал GROUP BY events.user_id недействителен и должен был вызвать ошибку.

Thorsten Kettner 04.05.2022 09:34

@ThorstenKettner SQLite похож на MySQL без ONLY_FULL_GROUP_BY. Это требование не выполняется. См. раздел о «голых столбцах» в sqlite.org/lang_select.html.

Barmar 04.05.2022 09:40

@ThorstenKettner Это еще хуже. SQLite имеет особый случай, если вы используете MIN() или MAX(). Пустые столбцы будут взяты из строки с минимальным/максимальным значением. Таким образом, ошибочные запросы, такие как SELECT name, MIN(somecol), делают то, что программисты интуитивно ожидают от них.

Barmar 04.05.2022 09:43

Да, я недавно узнал об этой штуке с голыми колонками. Но это хорошо задокументировано как улучшение, так что меня это устраивает. Что касается игнорирования функциональной зависимости, в их документах нужно читать между строк. Вы правы, они пропускают недопустимую агрегацию и просто называют пустой столбец name в запросе OP «неопределенным».

Thorsten Kettner 04.05.2022 09:54

Другие вопросы по теме