4

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.

luison
  • 1,850
  • 1
  • 19
  • 33

3 Answers3

8

Use find_in_set . The below will work.

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

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.

aF.
  • 64,980
  • 43
  • 135
  • 198
1

Try :-

SET @variousids="794,758";
SELECT * FROM table1 WHERE table1.ID FIND_IN_SET (table1.ID,@variousids)
Bajrang
  • 8,361
  • 4
  • 27
  • 40
  • Thanks, was just about to write about that was the solution I found. – luison Feb 06 '12 at 10:34
  • 2
    This one gives me an error, while the one in the other answer works... The 'table1.ID` right after the where cannot be used in MySQL 5.7 – Kukeltje Mar 16 '16 at 13:40