How to use IN statement in sql when both of side have multiple values

Question

I have a problem. I want to know how to use IN statement (in a query) when both sides have multiple values - I am writing a query :

SELECT name FROM math_Details WHERE cric_ids in (1,2,3,4,8);

In this query problem is in cric_ids column there is also multiple values seprated through (,comma); like (5,9,1,10,2)

Please help me?

you have a bad db design. learn how to make many-to-many relations — k102, Jun 07 '12 at 12:47
This is caused by failure to honour the first normal form of database design (1NF for short), which states that every column in a table must contain only 1 piece of data (the column must be atomic). By pushing a CSV list into a column you've violated that rule and as you've discovered there's a heavy price to pay. The book SQL Antipatterns contains some suggestions for working around this problem, but it also tells you in great detail why this kind of design is a bad idea. I'd recommend you pick a copy of that book up and take it to heart. http://pragprog.com/book/bksqla/sql-antipatterns — GordonM, Jun 07 '12 at 12:47
Can you try and create a http://sqlfiddle.com/ explaining you problem ? — Jerome WAGNER, Jun 07 '12 at 12:47

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

You could use MySQL's FIND_IN_SET() function repeatedly:

SELECT name
FROM   math_Details
WHERE  FIND_IN_SET(1, cric_ids) OR FIND_IN_SET(2, cric_ids)
    OR FIND_IN_SET(3, cric_ids) OR FIND_IN_SET(4, cric_ids)
    OR FIND_IN_SET(8, cric_ids)

Such a statement could be built dynamically in PHP. For example, using prepared statements in PDO:

$set = [1,2,3,4,8];
$sql = 'SELECT name FROM math_Details WHERE FALSE'
     . str_repeat(' OR FIND_IN_SET(?, cric_ids)', count($set));

$qry = $dbh->prepare($sql);
$qry->execute($set)

However, as others have said, this is an indication of poor database design. You should consider normalising your data structure so that instead of a cric_ids field, you have a table relating records in math_Details with each cric.

edited May 23 '17 at 12:29

Community

1
1

answered Jun 07 '12 at 13:25

eggyal

122,705
18
212
237

But there is restriction with this function.. that is i can use only on value each time. And i don't know how many values will come seprated through ','. – Mohammad Intsar Jun 08 '12 at 12:25
@Intsar: That is not entirely correct; the PHP given in the second block of code shows how to dynamically create the required SQL from a set of 5 values. – eggyal Jun 09 '12 at 08:49

score 0 · Answer 2 · edited Jun 07 '12 at 13:36

oh man, your design is so mess up that what you are asking is almost impossible, what I understand is that you have:

    Select name from math_Details where cric_ids in (1,2,3,4,8);

and the column looks like this:

| cric_ids |
-------------
|1,2,3,5,4 |
|5,8,6,12,3|
|78,6,2,4,6|
|5,7,8,9,1 |

and you want to be able to make search by digits....

the like wont help you because the result is a string or varchar, so your possible options could be (beside that you need to use a 'like' for each option)

critics like '1' //wont work at least you only have 1 in your cric_ids
critics like '%1' // will work only if 1 is the last value
critics like '1%' //will work only if 1 is the first vaue
critics like '%1%' // will give you all values with 1, like 1, 10, 11, 111, 19, etc...

I think you have 2 options,

make a full query and with PHP functions get the values (you can convert it to into an array and get the values), it will be SO SLOW!.....
Redesign your database (recommended) to fix that and futures conflicts.

Please take a look to database entity relationship model and normalization.

How to use IN statement in sql when both of side have multiple values

2 Answers2