How to use result from SELECT query in another one-CodePudding

I need to use selected rows in another query without selecting them again as subquery. Would be perfect if here possible to store them as variable.

What I mean:

/* First query: only one column returns (always), but multiple records, trying to store them as string */
SET @result := (SELECT GROUP_CONCAT(target_field SEPARATOR ',')
FROM table_one
WHERE condition;

/* Second query: need to pass saved array into IN() condition */
SELECT *
FROM table_two
WHERE id IN(@result);

But suddenly it won't work because @result processed as one string value, not as array.

Is it possible to pass variable as array? Any alternative solution (except subqueries) allowed.

CodePudding user response：

you could just use a subquery in your where condition. This should be ok for mySql

SELECT *
FROM table_two
WHERE id IN(SELECT id from table_one where condition_one);

CodePudding user response：

JOIN between tables can be used in this case:

SELECT *
FROM table_two
JOIN table_one ON table_two.id = table_one.target_field
WHERE table_one condition;

CodePudding user response：

You can use the function FIND_IN_SET():

SELECT *
FROM table_two
WHERE FIND_IN_SET(id, @result);

CodePudding user response：

If subquery it is not allowed you can use Store Procedure:

Try:

DELIMITER //
CREATE PROCEDURE my_procedure()
BEGIN
SET @result = "SELECT GROUP_CONCAT(target_field SEPARATOR ',') FROM table_one WHERE condition";
SET @finalqry = CONCAT("SELECT * FROM table_two WHERE id IN (", @result, ") ");
PREPARE stmt FROM @finalqry;
EXECUTE stmt;
END//
DELIMITER ;

Call it :

call my_procedure();