How to Fetch Multiple Rows as a Single Comma-Separated Row in MySQL?

Fetching Multiple Rows as a Single Comma-Separated Row in MySQL

Problem Statement:

You have two tables, "DISH" and "DISH_HAS_DISHES," where dishes are related with a one-to-many dependency. The goal is to retrieve data from both tables in a specific format: a comma-separated list of dish IDs and names for each meal.

Proposed Solution with GROUP_CONCAT Function:

MySQL provides the GROUP_CONCAT function, which allows for the concatenation of multiple rows based on a specified delimiter. Utilizing this function, you can achieve the desired output:

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

Explanation:

1. JOIN the "DISH_HAS_DISHES" (m) and "DISH" (d) tables based on the common dish_id field.
2. Use GROUP_CONCAT(dish_id) to concatenate the dish IDs into a comma-separated string for each meal. Store this in the dish_ids column.
3. Similarly, use GROUP_CONCAT(dish_name) to concatenate the dish names into a comma-separated string and store it in the dish_names column.
4. GROUP BY meal_Id ensures that the results are grouped by meal ID, resulting in a single row for each meal.

This query should produce the desired output format:

meal_id	dish_ids	dish_names
1	2,3,4	dish_2, dish_3, dish_4

The above is the detailed content of How to Fetch Multiple Rows as a Single Comma-Separated Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!