Concat is not working in inclause

[s.ganesh]

Hi,

Please help me, i try to fetch category list as like below, where category ids is passed in inclause, it returns two rows.

mysql> select c_id,c_name from category where c_id in (870,854);
+------+---------------+
| c_id | c_name        |
+------+---------------+
|  854 | Telugu        |
|  870 | Telugu Events |
+------+---------------+


Whereas same category id is concatenated and passed to inclause as parameter, but its returning only one row insted of two rows.

mysql> select c_id,c_name from category where c_id in (select concat(870,',',854) as c_id);
+------+---------------+
| c_id | c_name        |
+------+---------------+
|  870 | Telugu Events |
+------+---------------+

Please clarigy me.

Thanks.

[pritaeas]

CONCAT is a string function, am surprised you even get that one row. The query you get is:

select c_id,c_name from category where c_id in ('870,854');

My guess is that the string is being casted to an int again, resulting in 870.

[s.ganesh]

Hi,

Its returnig only one result as you mentioned, is it possible to get two rows by modifying something in the same qyery?

Thanks.

[pritaeas]

Yes, it's possible, but I recommend using the first one.

This should work I think:

SELECT c_id, c_name 
FROM category 
WHERE FIND_IN_SET(c_id, (SELECT CONCAT(870,',',854) AS c_id))

[s.ganesh]

Hi pritaeas,

Thanks a lot, it was working fine. :)

Thanks.