CODEWITHSUNDEEP
phpmysql
112233
112233

Reputation: 2466

How to query comma separated string?

Table_a

column name: list_id
record 1: 1,2,5,6,8
record 2: 1,3,2
record 3: 6,7,2
record 4: 9,8,0

Table_b

id ='2';

How to select records that have id='2' in the comma separated string? From the above example it should return record 1,2 and 3.

Query(How to amend this query, please?):

SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id;

Upvotes: 7

Views: 220

Answers (2)

ManiMuthuPandi
ManiMuthuPandi

Reputation: 1604

This should work for you

SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id and 
concat(',',Table_a.list_id,',') like '%,2,%'

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6844

use find_in_set function, but it is not optimized way you should normalize your data.

SELECT * FROM 
Table_a AS a 
JOIN Table_b AS b ON FIND_IN_SET(b.id,a.list_id)

Upvotes: 3

Related Questions