database - need query for desired output in mysql db -

i have 2 tables item , condition below db structure

item =>      id - integer     item_name - varchar  condition =>      cid - integer     cond_name  - varchar     item_array - varchar (stores item id's separated space                        like: 1 5 9 17 18  cid  cond_name  iem_array  1    c1         1 5 9  2    c2         9 17 18  3    c3         3 7 11  

in application in front end site have given option user can select 1 or more items based on have short list suggested items

for have created column item_array keeps information of related items separated array.

i need query list item_name item table based on related items of condition table. example if user selects 1 , 9 have display item id( 5 17 18 ) , corresponding item name. this.

id |  item_name ---+----------- 5  |  item1 17 |  item2 18 |  item3 

i tried write query

select substring(s.item,2, length(s.item))  ( select replace(item_array," ",",") item         `condition`         item "% 1 5 %") s  

above query shows me list of related item_array condition table if user selects 1 , 5. if user selects different values 1,9 or 1,17,18 kind of items query gets failed list related items.

how can related values. free make changes on tables structure without data redundancy.

please give valuable suggestions overcome problem. if other concept trigger can please guide me.

i think best option re-design condition table doesn't have item ids in single item array - should have 1 row per item id instead. in case should normalize data bit, example:

create table cond (  cid int primary key auto_increment,  cond_name varchar(128));  create table condition_items (  cid int,  item_id int);  insert cond values (1,'c1'); insert condition_items values (1,1),(1,5),(1,9);  insert cond values(2,'c2'); insert condition_items values (2,9),(2,17),(2,18); 

if using innodb use foreign keys enforce integrity:

alter table condition_items add foreign key (cid) references cond (cid); 

this allow straightforward joins performed between tables.

select item_id,item_name   cond   join condition_items using (cid)   join item on (item.id=condition_items.item_id)   cond_name="c2"; 

please note should avoid using condition table name reserved word.