MySql: return a list of Ids in a variable and reuse that variable in an IN CLAUSE

Question

Please see sample code

-- 1st select, getting AssetID from asset table
SELECT GROUP_CONCAT(AssetID)
INTO @AssetIDs
FROM  asset;

-- 2nd select, use @AssetIDs in an IN clause 
SELECT AssetID
from asset
where AssetID IN (@AssetIDs)

From the 1st select, it returns 10 IDs and those IDs get stored in @AssetIDs So i would imagine @AssetIDs = '1,2,3,4,5,6,7,8,9,10'

However, the second select always return the first ID, 1.

Can anyone help me find a way to select the list of IDs in a variable that reuse that variable in the IN clause?

Answer 1

I used a tmp table instead.

DROP TEMPORARY TABLE IF EXISTS tmpassetlist; 
CREATE TEMPORARY TABLE tmpassetlist AS
(
    SELECT AssetID
    FROM asset
    WHERE (condition)
);

Then do a join:

SELECT AssetID
from asset JOIN tmpassetlist
ON asset.AssetID = tmpassetlist.AssetID

I think it's also better performance wise because IN clause could get very slow when the list gets longer.