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?

Why use a variable at all? just in (select AssetID from asset) - If you reall have to do it see stackoverflow.com/questions/21101211/mysql-variable-in-clause — Alex K.
– Alex K., Commented Oct 4, 2016 at 14:57
@AlexK. and then it all just comes down to select AssetID from asset as the simplest query anyway with no IN — Drew
– Drew, Commented Oct 4, 2016 at 21:00
In order to evaluate where AssetID IN ('1,2,3,4,5,6,7,8,9,10'), MySQL implicitely converts your string '1,2,3,4,5,6,7,8,9,10' to int which it does by simply taking all digits from left, which is '1' only. So the query finds the record with ID 1. I don't like this conversion very much, I'd rather see it resulting in an error, because '1,2,3,4,5,6,7,8,9,10' obviously isn't an integer. But well, this is how the MySQL guys decided to do it. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 5, 2016 at 20:21
@ThorstenKettner ahh i see it now. That's why it's always returning the first ID — Vibol
– Vibol, Commented Oct 5, 2016 at 20:24

Vibol · Accepted Answer · 2016-10-05 20:10:52Z

6

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.

answered Oct 5, 2016 at 20:10

Vibol

8051 gold badge11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bob · Accepted Answer · 2024-07-16 19:38:47Z

1

I also wanted to find some ids then use them in IN clause in my procedure:

-- first find some ids
SELECT GROUP_CONCAT(AssetID)
INTO @AssetIDs
FROM  asset;

-- compose string query using found ids and execute
SET @query = CONCAT(
    "SELECT *
    FROM asset
    WHERE AssetID IN (",
    @AssetIDs,
    ")"
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Jul 16, 2024 at 19:38

bob

2,7641 gold badge32 silver badges46 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related