Combine Multiple child rows into one row MYSQL

Question

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;

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

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

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.

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

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.