How can i filter mysql data if a column has multiple comma separated values?

Question

I want to ask how can i filter mysql data if the condition is checked for a column with several comma separated values. I give you an example -

I have following table say "tbitems" -

id     item_names     item_types    item_features
1      item 1        8,6          10,5,4,9,8,6
2      item 2        8,6          10,5,8

Now suppose i want to fetch the records (items) that have item types either 8 or 6 but having item_features exactly equal to what we specify for example 10, 5, and 4.

I know for the first condition (item types) we can use IN operator.

But I don't know how we can filter the record based on item_features.

I can write the query like this -

SELECT * FROMtbitemsWHEREitem_typesIN (8) ANDitem_features` WHAT OPERATOR SHOULD COM HERE TO SELECT ONLY THESE VALUES ( 4, 5, 10)

If i use IN like i did for first condition then the second row of above table is also fetched because 5 and 10 are available in the set (10,5,8) but i want to extract only first row. Any idea?

EDIT -

Sorry, your workaround didn't work.

I wanted to get only first row of this table -

But now it is yielding some extra rows that i didn't want. And moreover i cant use so many FIND_IN_SET since there comma-separated values may be large in numbers. Also i couldn't use this sql in my PHP code.

Answer 1

For you current scenario you can do so by using FIND_IN_SET each time you need to match the item_features

SELECT * FROM
table1
WHERE
item_types
IN (8) AND FIND_IN_SET(4,item_features)
AND FIND_IN_SET(5,item_features)
AND FIND_IN_SET(10,item_features) 

Fiddle Demo

But i ask youif its possible to change the schema then first normalize your structure see Database normalization,store all the relations in a separate table ,like a junction table for item_types_relations which store the current table id and all the items id in a junction table as one-to-many and a same junction table for item_features_relation

EDIT As per @Ravinder's comment

SELECT * FROM
table1
WHERE
FIND_IN_SET(8,item_types)
AND FIND_IN_SET(4,item_features)
AND FIND_IN_SET(5,item_features)
AND FIND_IN_SET(10,item_features)