[mysql] Combine Multiple child rows into one row MYSQL

Thanks in advance, I just can't seem to get it!

I have two tables

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

What I am looking to output is a mysql query is something to this effect

Output

ID | Item_Name | Option_1 | Option_2
1    Pizza       Pepperoni  Extra Cheese
2    Stromboli     NULL     Extra Cheese

I have tried numerous options most ending in syntax error, I have tried group_concat but thats not really what I am looking for. I have a crude example below of what I think might be a start. I need the options to be in the same order every time. And in the program where the info is collected there is no way to reliable ensure that will happen. Is it possible to have them concatenate according to option number. Also I know that I will never have over 5 options so a static solution would work

Select Ordered_Items.ID,
    Ordered_Items.Item_Name,
FROM Ordered_Items
    JOIN (SELECT Ordered_Options.Value FROM Ordered_Options Where Option_Number = 43) as Option_1 
        ON Ordered_Options.Ordered_Item_ID = Ordered_Item.ID
    JOIN (SELECT Ordered_Options.Value FROM Ordered_Options Where Option_Number = 44) as Option_2 
        ON Ordered_Options.Ordered_Item_ID = Ordered_Item.ID;

Thanks! Joe

This question is related to mysql select join

The answer is


If you know you're going to have a limited number of max options then I would try this (example for max of 4 options per order):

Select OI.ID, OI.Item_Name, OO1.Value, OO2.Value, OO3.Value, OO4.Value

FROM Ordered_Items OI
    LEFT JOIN Ordered_Options OO1 ON OO1.Ordered_Item_ID = OI.ID
    LEFT JOIN Ordered_Options OO2 ON OO2.Ordered_Item_ID = OI.ID AND OO2.ID != OO1.ID
    LEFT JOIN Ordered_Options OO3 ON OO3.Ordered_Item_ID = OI.ID AND OO3.ID != OO1.ID AND OO3.ID != OO2.ID
    LEFT JOIN Ordered_Options OO4 ON OO4.Ordered_Item_ID = OI.ID AND OO4.ID != OO1.ID AND OO4.ID != OO2.ID AND OO4.ID != OO3.ID

GROUP BY OI.ID, OI.Item_Name

The group by condition gets rid of all of the duplicates that you would otherwise get. I've just implemented something similar on a site I'm working on where I knew I'd always have 1 or 2 matched in my child table, and I wanted to make sure I only had 1 row for each parent item.


I appreciate the help, I do think I have found a solution if someone would comment on the effectiveness I would appreciate it. Essentially what I did is. I realize it is somewhat static in its implementation but I does what I need it to do (forgive incorrect syntax)

SELECT
  ordered_item.id as `Id`,
  ordered_item.Item_Name as `ItemName`,
  Options1.Value
  Options2.Value
FROM ORDERED_ITEMS
LEFT JOIN (Ordered_Options as Options1)
    ON (Options1.Ordered_Item.ID = Ordered_Options.Ordered_Item_ID 
        AND Options1.Option_Number = 43)
LEFT JOIN (Ordered_Options as Options2)
    ON (Options2.Ordered_Item.ID = Ordered_Options.Ordered_Item_ID
        AND Options2.Option_Number = 44);

Here is how you would construct your query for this type of requirement.

select ID,Item_Name,max(Flavor) as Flavor,max(Extra_Cheese) as Extra_Cheese
    from (select i.*,
                    case when o.Option_Number=43 then o.value else null end as Flavor,
                    case when o.Option_Number=44 then o.value else null end as Extra_Cheese
                from Ordered_Item i,Ordered_Options o) a
    group by ID,Item_Name;

You basically "case out" each column using case when, then select the max() for each of those columns using group by for each intended item.


If you really need multiple columns in your result, and the amount of options is limited, you can even do this:

select
  ordered_item.id as `Id`,
  ordered_item.Item_Name as `ItemName`,
  if(ordered_options.id=1,Ordered_Options.Value,null) as `Option1`,
  if(ordered_options.id=2,Ordered_Options.Value,null) as `Option2`,
  if(ordered_options.id=43,Ordered_Options.Value,null) as `Option43`,
  if(ordered_options.id=44,Ordered_Options.Value,null) as `Option44`,
  GROUP_CONCAT(if(ordered_options.id not in (1,2,43,44),Ordered_Options.Value,null)) as `OtherOptions`
from
  ordered_item,
  ordered_options
where
  ordered_item.id=ordered_options.ordered_item_id
group by
  ordered_item.id

What you want is called a pivot, and it's not directly supported in MySQL, check this answer out for the options you've got:

How to pivot a MySQL entity-attribute-value schema


The easiest way would be to make use of the GROUP_CONCAT group function here..

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

Which would output:

Id              ItemName       Options

1               Pizza          Pepperoni,Extra Cheese

2               Stromboli      Extra Cheese

That way you can have as many options as you want without having to modify your query.

Ah, if you see your results getting cropped, you can increase the size limit of GROUP_CONCAT like this:

SET SESSION group_concat_max_len = 8192;

Joe Edel's answer to himself is actually the right approach to resolve the pivot problem.

Basically the idea is to list out the columns in the base table firstly, and then any number of options.value from the joint option table. Just left join the same option table multiple times in order to get all the options.

What needs to be done by the programming language is to build this query dynamically according to a list of options needs to be queried.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to join

Pandas Merging 101 pandas: merge (join) two data frames on multiple columns How to use the COLLATE in a JOIN in SQL Server? How to join multiple collections with $lookup in mongodb How to join on multiple columns in Pyspark? Pandas join issue: columns overlap but no suffix specified MySQL select rows where left join is null How to return rows from left table not found in right table? Why do multiple-table joins produce duplicate rows? pandas three-way joining multiple dataframes on columns