How to join multiple distinct rows in one column-CodePudding

I have 3 tables

Restaurants table:

restaurant_id	restaurant_name
1	Burger King
2	Coffee shop

Foods table:

food_id	food_title	category_id	restaurant_id
1	Burger with cheese	1	1
2	Beef Burger	2	1
3	Beef Burger with cheese	2	1
4	Iced Coffee	3	2
5	Dark Coffee	3	2
5	Green Tea	4	2

Categories table:

category_id	category_title
1	Normal Burger
2	Beef Burger
3	Coffee
4	Tea

The output I'm looking for:

restaurant_id	restaurant_name	categories_list
1	Burger King	[{category_id: 1, category_title: 'Normal Burger'}, {category_id: 2, category_title: 'Beef Burger'}]
2	Coffee shop	[{category_id: 3, category_title: 'Coffee'}, {category_id: 4, category_title: 'Tea'}]

CodePudding user response：

Try:

    select restaurant_id,restaurant_name,concat('[',group_concat(my_col),']') as tot_result
from ( select r.restaurant_id,r.restaurant_name,concat('{category_id: ', c.category_id," category_title: '",c.category_title ,"'}") as my_col
       from Restaurants r
       inner join Foods f on r.restaurant_id=f.restaurant_id
       inner join Categories c on c.category_id=f.category_id
       group by r.restaurant_id,r.restaurant_name,my_col
     ) as t1
group by restaurant_id,restaurant_name;

Result:

restaurant_id   restaurant_name   tot_result
    1            Burger King    [{category_id: 1 category_title: 'Normal Burger'},{category_id: 2 category_title: 'Beef Burger'}]
    2            Coffee shop    [{category_id: 3 category_title: 'Coffee'},{category_id: 4 category_title: 'Tea'}]

Demo

CodePudding user response：

Join Restaurants to Foods and Categories with LEFT joins and group by restaurant.
Then use JSON_ARRAYAGG() aggregate function to get the lists:

SELECT r.restaurant_id, r.restaurant_name,
       JSON_ARRAYAGG(JSON_OBJECT('category_id', c.category_id, 'category_title', c.category_title)) categories_list
FROM Restaurants r
LEFT JOIN (SELECT DISTINCT category_id, restaurant_id FROM Foods) f ON f.restaurant_id = r.restaurant_id
LEFT JOIN Categories c ON c.category_id = f.category_id
GROUP BY r.restaurant_id, r.restaurant_name;

I use SELECT DISTINCT ... in the table Foods because I see in your expected results that you want distinct categories in each list.

See the demo.