CODEWITHSUNDEEP
mysqlsql
Sachin
Sachin

Reputation: 1698

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

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 -

enter image description here

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.

Upvotes: 3

Views: 5995

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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)

Upvotes: 4

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

If values are stored in CSV format, you have to use FIND_IN_SET. Use of IN will not work.

mysql> select * from ( select '8,6' i ) r where i in (8);
+-----+
| i   |
+-----+
| 8,6 |
+-----+
1 row in set (0.00 sec)

mysql> select * from ( select '8,6' i ) r where i in (6);
Empty set (0.00 sec)

Try this: 

SELECT * FROM tbitems
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 )

Upvotes: 1

Related Questions