CODEWITHSUNDEEP
mysqlselectuser-defined
luison
luison

Reputation: 2110

mysql user variable within an IN where statement

I now gave up and sorted it by other means but because "curiosity killed the cat" I am trying to figure out how to use mysql query @variables within an IN where statement:

So, from this:

SELECT * FROM table1 WHERE table1.ID IN (794,758)

Try to include a variable and this returns a syntax error:

SET @variousids="(794,758)";
SELECT * FROM table1 WHERE table1.ID IN @variousids

This returns values only from the first:

SET @variousids="794,758";
SELECT * FROM table1 WHERE table1.ID IN (@variousids)

I've tried different syntax, values and have not found any specific doc about defining list of values.

Upvotes: 4

Views: 3555

Answers (3)

Bajrang
Bajrang

Reputation: 8639

Try :-

SET @variousids="794,758";
SELECT * FROM table1 WHERE table1.ID FIND_IN_SET (table1.ID,@variousids)

Upvotes: 1

user319198
user319198

Reputation:

Use find_in_set . The below will work.

SET @variousids="794,758";
SELECT * FROM table1 WHERE find_in_set(table1.ID,@variousids)

Upvotes: 8

aF.
aF.

Reputation: 66747

Use dynamic SQL:

SET @variousids="794,758";
prepare stmt from 'SELECT * FROM table1 WHERE table1.ID IN ( ? )'
execute stmt using @variousids;

For more info, check THIS article.

Upvotes: 1

Related Questions