How to get all Users and its orders as a sub-set in mysql

I Have a table like

user.id
user.firstname
user.lastname

another table I have is

order.id
order.userid
order.name
order.price

with this query:

SELECT user.firstname, user.lastname, order.name
FROM Customers
LEFT JOIN Orders ON user.id = order.userid;

If I have 3 users and each user have 3 order, I get 9 rows in results. how can I get the 3 rows having subsets of 3 order objects in them.

Here is an example of output I need

firstname | lastname | onderNames


im | ah | book, pen, watch


abc. | dff. | rice, wheat

thanks in advance

Answer

You are looking for an aggregation query. The most common structure is:

SELECT c.firstname, c.lastname, GROUP_CONCAT(o.name SEPARTOR ', ') as names
FROM Customers c LEFT JOIN
     Orders o
     ON c.id = o.userid
GROUP BY c.id, c.firstname, c.lastname;

This puts the result in a string. You can also put the result in JSON — which is also a string but formatted as a JSON array — using JSON_ARRAYAGG().