1

I am getting error when I run the below query in my database

Cardinality violation: 1241 Operand should contain 1 column(s)

SELECT `main_table`.*, COUNT( item1.product_id ) AS `i_count` FROM `sales_order` AS `main_table`
 INNER JOIN `sales_order_item` AS `item` ON main_table.entity_id = item.order_id AND
             item.parent_item_id IS NULL
 INNER JOIN `sales_order_item` AS `item1` ON main_table.entity_id = item1.order_id AND
             item1.parent_item_id IS NULL WHERE (`status` IN('pending', 'pending_payment')) AND (main_table.store_id = '1') AND (item.product_id = '36', '37') GROUP BY `main_table`.`entity_id` ORDER BY `i_count` DESC
 LIMIT 5;

That query though gives me an error:

"#1241 - Operand should contain 1 column(s)"

What stumps me is that but this query would be working fine. Selecting columns, then selecting two more from another table, and continuing on from there. I just can't figure out what the problem is.

Is there a simple fix to this, or another way to write my query?

2

You are doing wrong at (item.product_id = '36', '37') you can not use = with multiple value. You need to use IN instead.

  • 1
    when I am using this it shows the syntax error Now i am getting (item.product_id IN '36', '37'); but actually i need this (item.product_id IN ( '36', '37')); – chris Jun 7 '18 at 8:51
  • Please mark it as an excepted answer if that helps.! – Sandipan S Jun 7 '18 at 11:21
2

Try the following query:

SELECT `main_table`.*, COUNT( item1.product_id ) AS `i_count` FROM `sales_order` AS `main_table` INNER JOIN `sales_order_item` AS `item` ON (main_table.entity_id = item.order_id AND item.parent_item_id IS NULL) INNER JOIN `sales_order_item` AS `item1` ON (main_table.entity_id = item1.order_id AND item1.parent_item_id IS NULL) WHERE (`status` IN('pending', 'pending_payment')) AND (main_table.store_id = '1') AND (item.product_id IN ('36', '37')) GROUP BY `main_table`.`entity_id` ORDER BY `i_count` DESC LIMIT 5;
  • Thank u so much. – chris Jun 7 '18 at 7:13

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.