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

I Have a table like

another table I have is

with this query:

SELECT user.firstname, user.lastname,
FROM Customers
LEFT JOIN Orders ON = 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


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

SELECT c.firstname, c.lastname, GROUP_CONCAT( SEPARTOR ', ') as names
FROM Customers c LEFT JOIN
     Orders o
     ON = o.userid
GROUP BY, 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().