MySQL IN Operator

Question

hi friends, http://pastebin.ca/1946913 When i write "IN(1,2,4,5,6,7,8,9,10)" inside of the procedure, i get correct result but when i add the id variable in the "IN", the results are incorrect. I made a function on mysql but its still not working, what can i do?

Answer 1

Strings (broadly, variable values) don't interpolate in statements. vKatID IN (id) checks whether vKatID is equal to any of the values listed, which is only one: the value of id. You can create dynamic queries using PREPARE and EXECUTE to interpolate values:

set @query = CONCAT('SELECT COUNT(*) AS toplam
                    FROM videolar
                    WHERE vTarih = CURDATE() AND vKatID IN (', id, ') AND vDurum = 1;') 
PREPARE bugun FROM @query;
EXECUTE bugun;

Answer 2

You could use FIND_IN_SET( ) rather than IN, for example:

SELECT COUNT(*) AS toplam
FROM videolar
WHERE vTarih = CURDATE()
AND FIND_IN_SET( vKatID, id ) > 0
AND vDurum = 1

Sets have limitations - they can't have more than 64 members for example.

Answer 3

Your id variables is a string (varchar) not an array (tuple in SQL) ie you are doing the this in (in java)

String id = "1,2,3,4,5,6,7"

you want

int[] ids = {1,2,3,4,5,6,7}

So in your code

set id = (1,2,3,4,5,6,7,8,9,10)

I cannot help you with the syntax for declaring id as I don't know. I would suggest to ensure the code is easily updated create a Table with just ids and then change your stored procedure to say

SELECT COUNT(*) AS toplam
                        FROM videolar
                        WHERE vTarih = CURDATE() AND vKatID IN (SELECT DISTINCT id FROM idtable) AND vDurum = 1;

Hope this helps.