Menu
I have a Query which returns comma separated integers like :
select GROUP_CONCAT(ids) from table2
now I want to use that result in another query like :
select * from table1 where column in (select GROUP_CONCAT(ids) from table2)
in this case it will consider only first value in IN clause.
874
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
The SEPARATOR specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT function uses a comma (,) as the default separator. The GROUP_CONCAT function ignores NULL values. It returns NULL if there was no matching row found or all arguments are NULL values.
The GROUP_CONCAT() function has a default length of 1024 characters, which is controlled by the global variable group_concat_max_len . If the joined values length is greater than the group_concat_max_len value, then the result string will be truncated.
I agree with @Alma that this can't be done with IN, you might be able to do it with FIND_IN_SET, but if you can do it with IN it's probably a better approach :
SELECT *
FROM table1
WHERE find_in_set(ids, (
SELECT GROUP_CONCAT(ids)
FROM table2
)) != 0;
sqlfiddle demo
73
Any special reason not using a join and using a sub query
select * from table1 t1
JOIN table2 t2 on (t2.column = t1.ids)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
