0

I have table like this in MySQL

Id| name | types |
1 | John | 1,3   |
2 | Lena | 1     |
3 | Mika |4,6,5 .|

Where 'Types' is a list of numbers (its a derived varchar column).

Now how do I select records with specific types 1 or 2 or 3... This did not work as expected:

Select * from table where '1' IN (Types)

It only returns records with only type '1' (so only 2nd row in example) but I want to return all records that have '1' in Types (so records 1 and 2 in example) nevermind they have some other value in list also.

Thanks

Bernd Strehl
  • 2,852
  • 4
  • 24
  • 43
Genti
  • 1
  • 1

1 Answers1

1

Fix your data model! Do not store numbers as strings! You want a separate table with one row per "name" and per "type", a name_types table.

Sometimes, we are stuck with other people's really, really, really bad decisions. If so, you can use find_in_set():

Select *
from table
where find_in_set('1', Types)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786