У меня есть две таблицы: таблица 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"
}
]
Вы должны квалифицировать все столбцы с их таблицами, то есть on users.id = events.userId
вместо on id = userId
, чтобы СУБД знала, о каком идентификаторе вы говорите. Помимо этого запрос выглядит нормально и должен возвращать всех пользователей.
@ThorstenKettner спасибо за ответ. Обратите внимание на ответ Бармара ниже - это правильный ответ
Да, этого я не видел. Я мог бы, если бы все столбцы были квалифицированы с помощью таблиц, как указано. Тогда станет более очевидным, что вы группируете по столбцу таблицы с внешним соединением. Всегда уточняйте все столбцы при работе с более чем одной таблицей.
Попробуйте использовать вложенный 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 и пытаясь найти ответ. Я даже не могу представить, как долго и насколько широко мне придется практиковаться, чтобы приблизиться к тому же уровню, что и вы, чтобы иметь возможность просто посмотреть на запрос и увидеть, что не так. Еще раз спасибо!!!
Хорошо подмечено. Это то, что ускользнуло от меня. СУБД, конечно, должна выдавать ошибку, потому что имя пользователя функционально не зависит от идентификатора пользователя с внешним соединением. Ошибка в СУБД, вероятно, пресловутый читерский режим MySQL, где SET sql_mode = 'ONLY_FULL_GROUP_BY'
не применяется. @esentai: убедитесь, что вы всегда работаете в режиме ONLY_FULL_GROUP_BY, чтобы СУБД сообщала вам, когда ваш запрос агрегации недействителен.
@ThorstenKettner может, потому что я использовал sqlite, а не mysql или postgres?
@ThorstenKettner, как лучше всего включить режим only_full_group_by? это что-то, что я должен установить в файле конфигурации?
@ThorstenKettner Если users.id
является первичным ключом, то users.name
должен быть функционально зависимым.
Ой, извините, я перепутал СУБД. В SQLite такого режима нет. Так что это действительно ошибка, а не просто неправильная настройка СУБД.
@ Бармер: Правильно. Ваш GROUP BY users.id
действителен. Оригинал GROUP BY events.user_id
недействителен и должен был вызвать ошибку.
@ThorstenKettner SQLite похож на MySQL без ONLY_FULL_GROUP_BY
. Это требование не выполняется. См. раздел о «голых столбцах» в sqlite.org/lang_select.html.
@ThorstenKettner Это еще хуже. SQLite имеет особый случай, если вы используете MIN()
или MAX()
. Пустые столбцы будут взяты из строки с минимальным/максимальным значением. Таким образом, ошибочные запросы, такие как SELECT name, MIN(somecol)
, делают то, что программисты интуитивно ожидают от них.
Да, я недавно узнал об этой штуке с голыми колонками. Но это хорошо задокументировано как улучшение, так что меня это устраивает. Что касается игнорирования функциональной зависимости, в их документах нужно читать между строк. Вы правы, они пропускают недопустимую агрегацию и просто называют пустой столбец name
в запросе OP «неопределенным».
пожалуйста, опубликуйте образец данных - в виде текста