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

Answer 1

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;

Answer 2

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:

http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema

Answer 3

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);

Answer 4

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

Answer 5

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.