Use the result of a MySQL query, as a variable, with the IN-operator

Question

Looking for a way to return a variable that can be used inside a IN-operator.
My current result returns: 1,2,3, but I guess the variable should be more like this: '1','2','3' to be able to use it.

Is this possible?
Or should I try something else like explode, save the query,...

-- init vars
SET @export_date = '2022-06-20'; 
SET @order_ids = ''; 

-- Tasks on given day
SELECT * FROM tasks WHERE task_execution_date = @export_date;

-- method 1
SET @order_ids = (SELECT GROUP_CONCAT(DISTINCT task_order SEPARATOR ',' )  FROM tasks WHERE task_execution_date = @export_date); 
SELECT @order_ids; -- "1,2,3"

-- method 2
SET @order_ids = (SELECT GROUP_CONCAT(DISTINCT '' + cast( task_order  AS CHAR(50)) +'' )  FROM tasks WHERE task_execution_date = @export_date); 
SELECT @order_ids; -- "1,2,3"

-- method 3
SET @order_ids =  (SELECT GROUP_CONCAT( DISTINCT + '\'' + CAST(task_order AS CHAR (100)) + '\'') FROM tasks WHERE task_execution_date = @export_date); 
SELECT @order_ids; -- "1,2,3"

-- Orders by tasks 
 SELECT * from orders WHERE ordr_id IN (@order_ids);     -- returns only one result
 -- SELECT * from orders WHERE ordr_id IN @order_ids;    -- error
 SELECT * from orders WHERE ordr_id IN ('1', '2', '3');  -- works
 SELECT * from orders WHERE ordr_id IN (SELECT DISTINCT task_order FROM tasks WHERE task_execution_date = @export_date); -- works


-- Also needed: 
-- goods by orders
-- good_adrs by goods

Why are You not happy with Your last query? There are no array data structure in MySQL, You can define your own function, but why? Last query is what You should use — Bogdan Kuštan, Jun 20 '22 at 12:22
The last query is indeed a working one. But I also need the response of that solution for another `IN` (tasks->orders->goods->adr_codes->...) Creating my own function seems a good starting point — Aldo, Jun 20 '22 at 12:32
Use FIND_IN_SET() function. But string search, fullscan, slow... — Akina, Jun 20 '22 at 12:42
You don't need `DISTINCT`. You can use the last query as another subquery to get goods by orders, etc. — Bill Karwin, Jun 20 '22 at 13:31

score 1 · Answer 1 · answered Jun 20 '22 at 17:23

If You really, really, I mean REALLY know that these variables can't be evil, then You can build and execute raw query:

SET @export_date = '2022-06-20';
SET @order_ids = (SELECT GROUP_CONCAT(DISTINCT task_order SEPARATOR ',' )  FROM tasks WHERE task_execution_date = @export_date); 
SET @query = CONCAT('SELECT * from orders WHERE ordr_id IN (', @order_ids, ');');
PREPARE stmt FROM @query;
EXECUTE stmt;

Use the result of a MySQL query, as a variable, with the IN-operator

1 Answers1