The following works as expected when there is a single value stored in a variable.
SET @a := "20100630";
SELECT * FROM wordbase WHERE verified = @a;
But it does not work when there are multiple values stored in a variable.
SET @a := "'20100630', '20100701' ";
SELECT * FROM wordbase WHERE verified in (@a);
Do I need to use prepared statements for this?
There's good solution described here: https://stackoverflow.com/a/11957706/1523961
So you can use something like this:
SET @a := '20100630,20100701';
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);
Also, if you're selecting the ids for @a from another table, you can come up with the following:
SET @a := (SELECT GROUP_CONCAT(id) FROM someTable where yourBooleanExpressionHere);
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);
You cannot (as far as I am aware) store multiple values in a MySQL user defined variable. What you have done is create a string which contains:
'20100630', '20100701'
That is not two separate values, but a single string value, just as this is a single string value:
SET @a := "It's a single string, and that's the problem";
You need to use two separate variables, or prepare a statement, like this:
SET @a := "20100630";
SET @b := "20100701";
SET @sql = CONCAT(
'SELECT * FROM wordbase WHERE verified IN (',
@a,
',',
@b,
')'
);
SELECT @sql;
+--------------------------------------------------------------+
| @sql |
+--------------------------------------------------------------+
| SELECT * FROM wordbase WHERE verified IN (20100630,20100701) |
+--------------------------------------------------------------+
PREPARE stmt FROM @sql;
EXECUTE stmt;
But that's kinda messy. Why do you need to use variables?
Using GROUP_CONCAT and GROUP BY one could pull all values ( i.e. an id ) into a variable like so:
SET @var := (SELECT GROUP_CONCAT(id) FROM `table` WHERE `verified` = @verified GROUP BY verified);
Something like this should work. Is it ok to use prepared statements to create temporary tables like this?
SET @a := "'20100630', '20100701'";
SET @sql = CONCAT('create temporary table pn1 SELECT * FROM wordbase WHERE verified IN (', @a, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
select * from pn1;
FIND_IN_SET(column to find in , string csv) is a very handy method in case you have the string list of CSV:
SET @a := "'20100630', '20100701' ";
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);
if your variable is also coming from query then use this to set @a
SET @a := (SELECT GROUP_CONCAT(`id`) FROM `table`);
If you need to use your variable for a select or delete you can use select in the select:
delete from MPCurrentPayEntitlementAccrual where CurrentPayEntitlementID in ( select CurrentPayEntitlementID from MPCurrentPayEntitlement where PayRunID=myPayRunId by PayRunID desc);
That worked perfectly for me
SELECT GROUP_CONCAT(field_table1 SEPARATOR ',') FROM table1 into @var;
then
SELECT * FROM table2 WHERE field_table2 in(@var);
works fine for me
field_table1 after converting it to a string. Second query checks whether field_table2 is equal to that massive string.
GROUP_CONCAT(field_table1 SEPARATOR ',') builds a string like: 1,,2,,3, I think that GROUP_CONCAT(field_table1) is enough