Mysql dynamic query

Question

I have two table in mysql one tbl_a which has one int column id, second tbl_b which has one column song_ids in which data stored seapated by comma like 1,2,3,4

i want to select all id like this

select * from tbl_a where id in (select song_id from tbl_b)

but its giving me no record as its varchar HOW to do it in mysql

Answer 1

The proper way:

Change your database structure; it's not normalized. You should have a link table between tbl_a and tbl_b, where you have two fields: a_id and b_id. Then use a query such as:

SELECT * FROM tbl_a
INNER JOIN tbl_link ON tbl_link.a_id = tbl_a.id
INNER JOIN tbl_b ON tbl_b.id = tbl_link.b_id

The lazy, wrong way:

SELECT * FROM tbl_a INNER JOIN tbl_b
WHERE FIND_IN_SET(tbl_b.song_id, tbl_a.id)

Answer 2

that's wrong database design. tbl_b must have 2 columns, a_id and song_id both of int type

for the query you have to give out more details about tables relation

Answer 3

I would consider redesigning your tables before solving this problem. Why dela with the headaches of comma delimited data?