How to Retrieve Comma-Separated Dish IDs and Names for Each Meal in MySQL?

Retrieving Comma-Separated Values from Multiple Rows in MySQL

In this scenario, you have two tables, DISH and DISH_HAS_DISHES, with a one-to-many relationship. DISH contains dishes, while DISH_HAS_DISHES stores the relationship between meals and dishes. Your goal is to retrieve data in a format where each meal has a comma-separated list of dish IDs and dish names.

To achieve this, you can leverage MySQL's GROUP_CONCAT function. This function allows you to concatenate values from multiple rows into a single string, separated by a specified delimiter. Here's how to use it to address your requirement:

SELECT m.meal_Id,
        GROUP_CONCAT(dish_id) AS dish_ids,
        GROUP_CONCAT(dish_name) AS dish_names
FROM DISH_HAS_DISHES m
JOIN DISH d ON (m.dish_id = d.dish_id)
GROUP BY meal_Id

In this query:

• GROUP_CONCAT(dish_id) concatenates the dish IDs into a single string, separated by commas, and assigns it to the alias dish_ids.
• GROUP_CONCAT(dish_name) similarly concatenates the dish names.

The GROUP BY clause groups the results by meal_Id, ensuring that the comma-separated values are grouped according to each meal.

The output of this query will be a table with three columns:

• meal_Id: The meal ID.
• dish_ids: A comma-separated list of dish IDs associated with the meal.
• dish_names: A comma-separated list of dish names associated with the meal.

This will provide you with the desired result, where each meal has its respective dish IDs and names in a single comma-separated string.

The above is the detailed content of How to Retrieve Comma-Separated Dish IDs and Names for Each Meal in MySQL?. For more information, please follow other related articles on the PHP Chinese website!