CODEWITHSUNDEEP
mysql
pradeep
pradeep

Reputation: 1057

Mysql dynamic query

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

Upvotes: 1

Views: 584

Answers (3)

TheGeekYouNeed
TheGeekYouNeed

Reputation: 7539

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

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157872

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

Upvotes: 0

reko_t
reko_t

Reputation: 56430

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)

Upvotes: 2

Related Questions