How to Combine Multiple Child Rows into a Single Parent Row in MySQL?

Combining Multiple Child Rows into One Parent Row in MySQL

When dealing with structured tables that have nested data, it can be useful to merge multiple child rows into a single parent row to provide a more aggregated view of the data. One common scenario where this is needed is when working with order management systems, such as the one presented in the given example.

Consider two tables: Ordered_Item and Ordered_Options. The Ordered_Item table represents items ordered, while the Ordered_Options table includes additional details or options associated with each ordered item. The goal is to output a query that combines the Item_Name column from the Ordered_Item table with the corresponding option values from the Ordered_Options table, grouped in a specific order and separated by commas.

One effective solution is to utilize MySQL's GROUP_CONCAT function, which concatenates values from multiple rows, grouped by a specified column. The query below accomplishes the desired result:

SELECT
  ordered_item.id AS `Id`,
  ordered_item.Item_Name AS `ItemName`,
  GROUP_CONCAT(Ordered_Options.Value) AS `Options`
FROM
  ordered_item
JOIN
  ordered_options
ON
  ordered_item.id = ordered_options.ordered_item_id
GROUP BY
  ordered_item.id

This query will produce the following output:

Id  ItemName         Options
1   Pizza            Pepperoni,Extra Cheese
2   Stromboli        Extra Cheese

The GROUP_CONCAT function ensures that the option values are concatenated into a single string and preserves the order of the options based on their Option_Number column. This method is highly efficient and can handle a large number of options without requiring a static solution or any additional complexity in the query.

To handle cases where the concatenated result exceeds the default maximum length of 1024 characters, MySQL provides the group_concat_max_len variable. Setting this variable to a higher value, as shown below, will accommodate larger concatenated strings:

SET SESSION group_concat_max_len = 8192;

The above is the detailed content of How to Combine Multiple Child Rows into a Single Parent Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!