Concatenating Multiple Child Rows into a Single Row in MySQL
When working with relational databases, it is often necessary to combine data from multiple related rows into a single row. This task can be accomplished using the GROUP_CONCAT function in MySQL.
Problem Statement:
Consider two tables: Ordered_Item and Ordered_Options. The Ordered_Item table stores items ordered, while the Ordered_Options table records the options associated with each ordered item. The goal is to create a query that outputs a single row for each ordered item, with the options included as separate columns.
Solution:
The following query combines rows from the Ordered_Item and Ordered_Options tables using the GROUP_CONCAT function:
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 uses the GROUP_CONCAT function to concatenate the values of the Value column from the Ordered_Options table, separated by commas. The concatenated values are assigned to the Options column in the output.
Output:
The output of the query will resemble the following:
| Id | ItemName | Options | | --- | -------- | ------- | | 1 | Pizza | Pepperoni,Extra Cheese | | 2 | Stromboli | Extra Cheese |
Note:
To accommodate potential differences in the order of options, consider using a CASE statement to order the values by Option_Number before concatenating them using GROUP_CONCAT.
The above is the detailed content of How to Concatenate Multiple Child Rows into a Single Parent Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!