I've two tables : DISH and DISH_HAS_DISHES. Dish table has all of the dishes and "Dish_has_dishes" table includes a one-to-many relationship with "Dish" table. I.e. a dish might have multiple dishes. For instance
dish_id dish_name 1 dish_1 2 dish_2 3 dish_3 4 dish_4
meal_id dish_id 1 2 1 3 1 4
Here meal_id and dish_id both of them are IDs from DISH table. Now I would like a format such as this:
meal_id dish_ids dish_names 1 2,3,4 dish_2, dish_3, dish_4
That's comma separated dish id and names for every meal. How to achieve that?
Use GROUP_CONCAT FUNCTION
SELEct m.meal_Id, GROUP_CONCAT(dish_id) dish_ids, GROUP_CONCAT(dish_name) dish_names FROM DISH_HAS_DISHES m JOIN DISH d ON (m.dish_id = d.dish_id) GROUP BY meal_Id