1

I have an SQL table as given below

  title                     genre
--------------------|----------------------         
The Dark Knight     |  Action, Crime, Drama
The Godfather       |  Crime,Drama
Inception           |  Sci-Fi

How can I write a Select query such that all titles with genre "Crime" will be displayed

devz
  • 19
  • 6
  • https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Strawberry Oct 25 '20 at 16:48

1 Answers1

0

First, you should fix your data model! Storing multiple values in a column is a really, really, really bad idea.

That said, sometimes we are stuck with other people's really, really, really bad decisions. MySQL has a convenient function find_in_set() for this purpose. But you need comma-separate values with no spaces. So:

select t.*
from t
where find_in_set('Crime', replace(genres, ' ', '')) > 0;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks @GordonLinoff This did the trick! Really appreciate it Just curious to know in a similar situation like here where I a movie could have multiple genres is there a better way to store it in a DB (or do you have a suggested way to go about it )? – devz Oct 25 '20 at 16:37
  • @devz. . . .You should have a table `movieGenres` with one row per movie and per genre. This is the standard way of representing a many-to-many relationship in SQL. – Gordon Linoff Oct 25 '20 at 18:26
  • Got it ! Appreciate your help – devz Oct 25 '20 at 18:27