Search For The Comma Separated Values In MYSQL Column

Question

genreTable:

id	genre
1	Pop
2	Rock
3	Electro

songTable:

id	name	genre
1	Song1	1
2	Song2	1,2
3	Song3	2,3

Problem: Lets say I want to build query like:

SELECT * FROM songTable WHERE genre = '1'

It'll only return Song1

But how Do I make sure it also returns Song1, Song2

Any other suggestions regarding re-structuring the table is also accepted...

Answer 1

You should fix your data model! There are many reasons why your data model is broken:

• Columns should only contain one value.
• Numbers should be stored as numbers, not strings.
• Foreign key relationships should be properly declared.
• SQL has pretty bad string processing capabilities.

Sometimes, you are stuck with other people's really, really, really bad design decisions. In that case, you can use find_in_set():

select s.*
from songTable s
where find_in_set('1', genre) > 0