0

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?

  • 1
    One of your sub-queries must be returning > 1 row. Have you ran each in isolation? – Molenpad Apr 22 '16 at 10:21
  • @Molenpad Yes, it returns more than one row, however, the root query returns also the same number of rows. – androniennn Apr 22 '16 at 10:31
  • 1
    @androniennn At the minute it has no idea how to map the subquery rows to the outer query. – Mark Sinkinson Apr 22 '16 at 10:32
  • A subquery, when used as part of the select list in the way you have demonstrated, cannot return more than 1 row. The error message is explicit in advising this. – Molenpad Apr 22 '16 at 10:33
  • @Molenpad : With order_id equals to 1, I just have one row . I mean that query can return just one row . – androniennn Apr 22 '16 at 10:37
3

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  
  • I think that it's working now, I just added a MAX before the CASE for the three ones. Can you please edit your answer to be able to accept it ? – androniennn Apr 22 '16 at 11:07
  • @Lennart : Thank you very much for your hint. – androniennn Apr 22 '16 at 11:24
  • This may be just sweeping the schema flaw under the rug. – Rick James Apr 25 '16 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.