Combine Multiple child rows into one row MYSQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Combine Multiple child rows into one row MYSQL

Examples related to mysql

Examples related to select

Examples related to join