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

Question

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?

Answer 1

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;

Answer 2

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