0

table Name Data

ID  | Name | List
-----------------
1   | Test 1 | 1,2,4,6
2   | Test 2 | 2,4
3   | Test 3 | 4,6,7
3   | Test 4 | 2

how can select items from 'data' table where 'list' have 2 ?

after run query want see this result :

ID  | Name | List
-----------------
1   | Test 1 | 1,**2**,4,6
2   | Test 2 | **2**,4
3   | Test 4 | **2**
Javid
  • 1
  • 1
    You should normalize your table. 1 row per value. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – chris85 Feb 09 '16 at 20:30
  • 1
    Yes, as @chris85 states. This is a horrible way to do it and will cause endless headaches. – AbraCadaver Feb 09 '16 at 20:31
  • Use find_in_set(2,list) – devpro Feb 09 '16 at 20:33
  • 2
    quick/dirty hack: [find_in_set()](http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set). proper fix: normalize your design before you cause yourself any more trouble. – Marc B Feb 09 '16 at 20:33
  • @marc-b agreed bro.. Best way to normalized yur table else use find in set or LIKE – devpro Feb 09 '16 at 20:34

1 Answers1

3

Suggestion:

First of all you need to normalize your table structure as one list Id in one row else you can use FIND_IN_SET

Solution:

SELECT * FROM Data WHERE FIND_IN_SET(2,List);

Suggestion 2:

One more suggestion you can also create a separate table for saving list Id against data id benefit is that name field which is string column will not repeat just suggestion.

devpro
  • 16,184
  • 3
  • 27
  • 38