MySQl merging rows where a condition is true

I've spent a couple of days trying to solve this with limited success, I'm sure there's a simple answer but with no luck so far despite quite a lot of research, I fear the problem is my limited SQL knowledge.

I have 3 tables. orders, orders_products and orders_products_attributes.

I'm trying to return a results table for a given order, showing all orders_products that match, and merging in orders_products_attributes rows for that product where it exists (sometimes there are no attributes)

I've got to here:

SELECT      oi.orders_id AS orderid
,           oi.products_id AS ItemNumber
,           oi.products_quantity AS Quantity
,           oi.final_price AS CostPerUnit
,       oi.products_name AS ItemTitle
,       concat (patt.products_options_id, '-', patt.products_options_values_id) AS OptionCodes
,       concat (patt.products_options, ': ', patt.products_options_values) AS OptionNames
FROM        orders_products oi
JOIN        orders o
ON      o.orders_id = oi.orders_id
LEFT JOIN   orders_products_attributes patt
ON          patt.orders_products_id = oi.orders_products_id AND patt.orders_id = oi.orders_id
WHERE       o.orders_status =2 AND oi.orders_id =10346
ORDER BY    ItemNumber

Which is returning the following results:

orderid ItemNumber Quantity CostPerUnit ItemTitle OptionCodes OptionNames

10346   140        1        319.9982    Item 1    3-5        Choice: A

10346   140        1        319.9982    Item 1    1-1        Choice2: B

10346   210        1        112.5000    Item 5    NULL       NULL

I'm trying to merge rows where ItemNumber is the same, concat'ing OptionCodes and OptionNames where they exist to end up with:

orderid ItemNumber Quantity CostPerUnit ItemTitle OptionCodes OptionNames

10346   140        1        319.9982  Item 1   3-5, 1-1    Choice: A, Choice2: B

10346   210        1        112.5000  Item 5    NULL         NULL

I've been trying to GROUP_CONCAT these but开发者_运维技巧 only succeed in merging all 3 rows.

Sorry if I'm being slow, but I just can't seem to move on from here.

TIA, Andrew

You could group by the unique key of the order_products table. Since I wasn't sure what that was, I just grouped by all the columns in the Select clause other than the ones on which I use Group_Concat.

Select oi.orders_id As orderid
    , oi.products_id As itemnumber
    , oi.products_quantity As quantity
    , oi.final_price As costperunit
    , oi.products_name As itemtitle
    , Group_Concat( Concat(patt.products_options_id, '-', patt.products_options_values_id) ) As optioncodes
    , Group_Concat( Concat(patt.products_options, ': ', patt.products_options_values) ) As optionnames
From orders_products oi
    Join orders o
        On o.orders_id = oi.orders_id
    Left Join orders_products_attributes patt
        On patt.orders_products_id = oi.orders_products_id 
            And patt.orders_id = oi.orders_id
Where o.orders_status =2 
    And oi.orders_id =10346
Group By oi.orders_id, oi.products_id
    , oi.product_quantity, oi.final_price, oi.products_name
Order By itemnumber

You should be able to use GROUP_CONCAT like you said, but in order to do so, you must also GROUP BY ItemNumber.