sql - Mysql :Exclude row that does not satisfy the condition list -

so here data

id  c1        c2               c3   6             digit 2          6,8,10,12 12            digit 3          15 15  127       digit 2          6,7,8,9,10,11,12,13 68  140,141   digit 11         85,86,87,88,167,168,158,159 73  1         digit 11         85,86,87,88,169,170 76            digit 11         85,86,87,91,164,165,166,167,168 99            digit 11         20,27,85,86,87 106           digit 1          1,2 111           digit 11         85,86,87,88 112           digit 11         85,86,87,88 135           digit 11         85,86,87 

and condition string (2,6,15,37,42,52,62,65,79,85,94,100,104,107,113,124,131)

now,i want exclude row 3,4,5 if values 127,140,141,1 not in list condition. tried not in , no avail. think might missing basic, cant it.

it's better not store multiple values in column if possible. it's easier queries this.

you cannot use "in" or "not in" because looking list of separate items. c3 1 item happens have commas in it.

try this:

select *  (select id, c1, c2, concat('|',replace(c3,',','|'),'|') c3 `table` `c3` ) t1  t1.c3 not "|127|" , t1.c3 not "|140|" , t1.c3 not "|141|" , t1.c3 not "|1|" 

you avoid "|" , concat "," start , end.

or fix database schema acts normalized relational database.

every column contains multiple values should separated out own table.

there should no column c3 in table above. instead, should have table, some_other_data:

at point, see c3=6 related more 1 record in main table. therefore, need third, linking table, in addition some_other_data. see below.

`some_other_data` id 6 8 10 12 15  `main_table_to_some_other_data_link` some_other_data_id | main_table_id 6                    6 8                    6 10                   6 12                   6 15                   12 6                    15 

etc. can see linking table can contain duplicates of either value. other 2 tables have unique ids.