Is there way to return null valued rows when when querying joined table-CodePudding

I have two tables: users table with id, name columns and events table with id, content and userId columns.

I am trying to query a table that return joined information from these two tables with name and events columns where events would represent an array of content fields corresponding to a user.

This is the query I am running:

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

However rows with null values are not being returned except of just one row. What am I doing wrong?

Users table

[
{
"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"
}
]

Events table

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

Joined table

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

CodePudding user response：

You should group by the column in the parent table, not the table being left joined, so that the values will never be null.

So change GROUP BY userid to GROUP BY users.id.

CodePudding user response：

Try to use a nested SELECT, this should return null for the users without any event:

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