Select another column on IN query (subquery)

Question

I have a query that gets a sku_product product that is sold on a precise date

SELECT stock_products.`related_warehouse_position_id`, `product_code`, `EAN_CODE`, `custom_cart_picked_up`, `warehouse`, sum(`RemainingStock`), stock_products.`time_Picked_up` 
FROM `product_warehouse_position` 
INNER JOIN stock_products ON product_warehouse_position.id = stock_products.related_warehouse_position_id 
WHERE stock_products.time_Picked_up < '2017-10-10' 
GROUP BY product_code 
HAVING SUM(RemainingStock) = 0

Now i've tried with this query but the result is wrong.

I've try to make a subquery, but i need to get a time_pickedUp which is located on stock_products table.

SELECT `id` ,`product_code`, `EAN_CODE`, `custom_cart_picked_up`, `warehouse`, sum(`RemainingStock`) 
FROM product_warehouse_position 
WHERE product_code IN (
    SELECT product_stock_sku, time_Picked_up 
    FROM `stock_products` 
    WHERE stock_products.time_Picked_up < '2017-05-01'
) 
GROUP BY product_warehouse_position.product_code 
HAVING SUM(product_warehouse_position.`RemainingStock`) = 0

This query return an error :

Operand should contain 1 column(s).

How to solve this problem without change the structure of this query?

What do you mean the "result is wrong"? What is the desired output? Also, your `WHERE ... IN` clause doesn't make sense... you're comparing `product_code` to either `product_stock_sku` or `time_picked_up` — kchason, Nov 06 '17 at 15:50
The first query not actually show all items with the sum = 0 — Dev E-reall, Nov 06 '17 at 15:51
In your inner select you can only select the column that you want to match. Did you try it? — isaace, Nov 06 '17 at 16:03
Yes, but not work. Is there a way to take that field in the subquery? — Dev E-reall, Nov 06 '17 at 16:09

score 0 · Answer 1 · answered Nov 06 '17 at 15:51

0

If your query is correct then the only thing you need to do is remove the column time_Picked_up from the select in your inner query. You can still filter on that column in your WHERE clause.

answered Nov 06 '17 at 15:51

isaace

3,336
1
9
22

Select another column on IN query (subquery)

Operand should contain 1 column(s).

1 Answers1