MySQL Query with IN return empty

Question

I had an issue of while run a query with IN key return empty. Here's my query:

 SELECT * FROM `sc_miscellaneous` where discount_for=4 AND stud_id IN (1,2) AND status=1

This query returns a correct results. But if i use IN (2,3) or IN (2) means zero results. Eg.

SELECT * FROM `sc_miscellaneous` where discount_for=4 AND stud_id IN (2,3) AND status=1

Table sc_miscellaneous:

ID Miscellaneous misc_amount discount discount_for class stud_id               status
1  5                200        2        4            1    1,2,3,4,5,6,            1  
                                                          7,8,9,10,11, 
                                                          12,13,14,15,16,
                                                          17,18,19 

2  6                500        2        4            1    1,2,3,4,5,6,7           1

Advance Thanks.

refer [this](http://stackoverflow.com/questions/10480568/comma-separated-values-in-mysql-in-clause) or [this](http://dba.stackexchange.com/questions/52492/mysql-using-comma-separated-values) — Prasad Khode, Dec 26 '14 at 07:01

Pratik Joshi · Answer 1 · 2014-12-26T09:39:30.140

1

Updated

Use following query

SELECT * FROM `sc_miscellaneous` 
WHERE stud_id LIKE '%,2,%' OR  stud_id LIKE '%,3,%' 
  OR  stud_id LIKE '2,%,' OR  stud_id LIKE '3,%'
  OR  stud_id LIKE '%,2' OR  stud_id LIKE '%,3'

As you are having varchar comma separated list.

Check

1)if that UNIQUE number is in between comma separated list,

2)OR if is that at the start

3)OR if its at the end.

edited Dec 26 '14 at 09:39

answered Dec 26 '14 at 09:07

Pratik Joshi

11,485
7
41
73

This will also recover, for example, stud_id = 12 or stud_id = 35. If you're going comma seperated (which you shouldn't) then the comma's need to be included in the LIKE ( '%,2,%' ) and you need to add a comma at the start and end of the value in the column. – Erik Dec 26 '14 at 09:30
but if we use LIKE means, column have '22,32' means its also selected. but we need exact result. -thanks – Krsmoorthi Dec 26 '14 at 09:31
Thanks Friends, i got it. @Erik and jQuery PHP – Krsmoorthi Dec 26 '14 at 09:53

Vamshi .goli · Answer 2 · 2014-12-26T09:26:05.680

0

I think your stud_id type was int make it to varchar and run the code

stud_id(int)---->change to varchar

you have to work with

in_array()

edited Dec 26 '14 at 09:26

answered Dec 26 '14 at 07:18

Vamshi .goli

522
4
13

type of the stud_id is varchar only. – Krsmoorthi Dec 26 '14 at 07:53

score 0 · Answer 3 · answered Dec 26 '14 at 08:34

0

I don't think the way you are writing your query using IN clause is the right way.

Please follow this link

According to the documentation, if stud_id is an integer then you can check it with multiple integers using IN Clause. Like if stud_id = 1, then you can write stud_id IN ( 1,2,3).

For your requirement you may have to write multiple queries for extracting data and compare.

answered Dec 26 '14 at 08:34

dipak_pusti

1,645
2
23
42

if change the type of stud_id as int means then how it allows comma. I like to insert all the student id's in a single record instead of insert no. of records. But 1st query is working fine, the issue is in 2nd only, if we select center value(except 1) in stud_id column means no result. – Krsmoorthi Dec 26 '14 at 09:13

MySQL Query with IN return empty

3 Answers3