Subquery returns more than 1 row

Question

I'm executing a query to return the products of a specific sale in Magento database.

It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row when the sale contains more than one product/row.

Query:

SELECT  name,
        qty_ordered,
        item_id,
        original_price,
        discount_percent,
        price,
        tax_percent,

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "cip") AS cip, /*Select CIP*/

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "cip7") AS cip7, /*Select CIP7*/

        (SELECT mcpev.value
        FROM magento_sales_flat_order_item msfoi,
        magento_catalog_product_entity_varchar mcpev,
        magento_eav_attribute mea
        WHERE order_id = 1
        AND msfoi.product_id = mcpev.entity_id
        AND mcpev.attribute_id = mea.attribute_id
        AND mea.attribute_code = "gtin_13") AS gtin13 /*Select GTIN13*/

FROM    magento_sales_flat_order_item
WHERE   order_id = 1

I do not see why this error occurs, despite that the root select only (without the inner ones) returns 3 rows, and the inner selects returns also 3 rows .

How can I resolve the issue?

Answer 1

My MySQL knowledge is rusty (at best), but subqueries like this can normally be replaced by a JOIN and GROUP BY to achieve the desired result.

Something like this will get you on your way:

SELECT  name,
        qty_ordered,
        item_id,
        original_price,
        discount_percent,
        price,
        tax_percent,
        MAX(CASE WHEN mea.attribute_code = 'cip' THEN mcpev.value END) AS cip,
        MAX(CASE WHEN mea.attribute_code = 'cip7' THEN mcpev.value END) AS cip7,
        MAX(CASE WHEN mea.attribute_code = 'gtin_13' THEN mcpev.value END) AS gtin13
FROM    magento_sales_flat_order_item msfoi
INNER JOIN magento_catalog_product_entity_varchar mcpev
  ON msfoi.product_id = mcpev.entity_id
INNER JOIN magento_eav_attribute mea
  ON mcpev.attribute_id = mea.attribute_id
WHERE   order_id = 1
GROUP BY name, qty_ordered, item_id, original_price, discount_percent, price, tax_percent