是否有方法返回空值行时，查询连接表

Question

我有两个表: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"
}
]

Answer 1

应该根据父表中的列进行分组，而不是根据左连接的表进行分组，以便值永远不会为空。

将GROUP BY userid改为GROUP BY users.id

Answer 2

尝试使用嵌套的SELECT，这应该为users返回null而没有任何事件:

select 
u.name, 
SELECT(
group_concat(content)  
FROM 
events 
WHERE 
userId = u.id
) as events 
from 
users u
order by 
u.id