How to select a parent and all children?

Question

MySQL

There is a table category :

You need to select the parents and all child categories . I am using the following query:

SELECT t1.name AS parent, t2.name as children
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent_id = t1.id
WHERE t1.parent_id IS NULL
ORDER BY t1.id

Result:

I would like to combine all the children into one element.
Example:

How can this be implemented?

Answer 1

I would like to combine all the children into one element.

This is not a question of sampling, but of processing the results.
The database is not responsible for the output format of the requested data.
In PHP, using PDO, for such a simple query, you can get a grouping by combining the FETCH_GROUP and PDO::FETCH_COLUMN modes.
But with a higher nesting level, or if you need to get any more data than the name, you will need to write the handler by hand.

Answer 2

https://dev.mysql.com/doc/refman/8.0/en/with.html
recursive CTE