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.

What is the type of stud_id?

Ataboy Josef
– Ataboy Josef

2014-12-26 06:49:55 +00:00
Commented Dec 26, 2014 at 6:49 — Ataboy Josef
– Ataboy Josef, Commented Dec 26, 2014 at 6:49
refer this or this

Prasad Khode
– Prasad Khode

2014-12-26 07:01:41 +00:00
Commented Dec 26, 2014 at 7:01 — Prasad Khode
– Prasad Khode, Commented Dec 26, 2014 at 7:01

Pratik Joshi · Accepted Answer · 2014-12-26 09:39:30Z

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, 2014 at 9:39

answered Dec 26, 2014 at 9:07

Pratik Joshi

11.7k7 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erik Over a year ago

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.

Krsmoorthi Over a year ago

but if we use LIKE means, column have '22,32' means its also selected. but we need exact result. -thanks

Krsmoorthi Over a year ago

Thanks Friends, i got it. @Erik and jQuery PHP

Dipak Kumar Pusti · Accepted Answer · 2014-12-26 08:34:57Z

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, 2014 at 8:34

Dipak Kumar Pusti

1,7232 gold badges23 silver badges42 bronze badges

1 Comment

Krsmoorthi Over a year ago

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.

Vamshi .goli · Accepted Answer · 2014-12-26 09:26:05Z

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, 2014 at 9:26

answered Dec 26, 2014 at 7:18

Vamshi .goli

5204 silver badges13 bronze badges

1 Comment

Krsmoorthi Over a year ago

type of the stud_id is varchar only.

Collectives™ on Stack Overflow

MySQL Query with IN return empty

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related