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

Combining Child Rows into One Parent Row in MySQL

The challenge presented here involves combining data from two tables: Ordered_Item and Ordered_Options. The goal is to output a single row for each item in Ordered_Item, including a concatenation of all options associated with that item from Ordered_Options.

Database Structure

**Ordered_Item**
ID | Item_Name
1  | Pizza
2  | Stromboli

**Ordered_Options**
Ordered_Item_ID | Option_Number | Value
1               43         Pepperoni
1               44         Extra Cheese
2               44         Extra Cheese

Proposed Solution

The provided query attempt, while not syntactically correct, demonstrates a possible approach using multiple joins. However, a more efficient solution can be achieved using the GROUP_CONCAT function.

GROUP_CONCAT allows for the concatenation of values from multiple rows into a single string. The following query utilizes GROUP_CONCAT to achieve the desired output:

select
  ordered_item.id as `Id`,
  ordered_item.Item_Name as `ItemName`,
  GROUP_CONCAT(Ordered_Options.Value) as `Options`
from
  ordered_item,
  ordered_options
where
  ordered_item.id=ordered_options.ordered_item_id
group by
  ordered_item.id

Output

Id              ItemName       Options

1               Pizza          Pepperoni,Extra Cheese

2               Stromboli      Extra Cheese

Additional Considerations

• Multiple Options: This solution can handle any number of options per item without requiring additional modifications to the query.
• Truncation: If the concatenated result exceeds the default maximum length, consider using SET SESSION group_concat_max_len = 8192; to increase the limit.

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