CODEWITHSUNDEEP
phpmysql
Krsmoorthi
Krsmoorthi

Reputation: 31

MySQL Query with IN return empty

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.

Upvotes: 1

Views: 94

Answers (3)

Pratik Joshi
Pratik Joshi

Reputation: 11693

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.

Upvotes: 1

Vamshi .goli
Vamshi .goli

Reputation: 520

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()

Upvotes: 0

Dipak Kumar Pusti
Dipak Kumar Pusti

Reputation: 1723

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.

Upvotes: 0

Related Questions