I have a table in which there is a column which stores id(s) of another table, there could be a single id or multiple ids separated by a comma (,).

I want to search for the row(s) where the id is available, id can be available in any of the column whether it has a single id or multiple ids.

| id | s    |
 ---- ------ 
|  1 | 2    |
|  2 | 1    |
|  3 | 2    |
|  4 | 2,3  |
|  5 | 1,3  |
|  6 | 1,2,5|
 ---- ------ 

So if I use

SELECT * FROM tablename WHERE other_table_id=2

It should return 4 rows as the id 2 is available in 4 different rows, but it only returns 3 rows where the id 2 is at the beginning.

Why is it not including the last row where id 2 is in the middle? How can I resolve this?

Please help!

CodePudding user response：

You could use FIND_IN_SET , but it will be very slow for larger data

SELECT * 
FROM tablename 
WHERE FIND_IN_SET(2,s)>0;

Demo

Or you could use CONCAT

select * 
from tablename 
where CONCAT(',', s, ',') like '%,2,%'

Demo