Home > Database > Mysql Tutorial > How to Concatenate Multiple Child Rows into a Single Parent Row in MySQL?

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

Mary-Kate Olsen
Release: 2024-11-28 07:36:13
Original
737 people have browsed it

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

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;
Copy after login

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 |
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template