CODEWITHSUNDEEP
mysqlsql
sumit1024
sumit1024

Reputation: 41

Mysql is returning empty result when i use not in (column_name)

I am executing

select * from tbl_test where 2 in (c1)

It should return the row but still it's return empty row and when i use

select * from tbl_test where 2 in (1,2,3)

mysql returns a row . 

 c2 | c3 | c4 |  c1
----+----+----+------
 1  | 3  | 4  | 1,2,3

Upvotes: 1

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You have a major confusion. You are confusing a string with 5 characters with a list of 3 integers. These two things are simply different. Don't confuse them.

This confusion has then led you (or someone else) to store a list in a single column. In a relationship database, a column should represent a single value, not a list. SQL has this really great way of implementing lists. It is called a table.

In MySQL, you can do what you want using find_in_set():

select *
from tbl_test
where find_in_set(2, col3) > 0;

However, the right solution is to fix your data model to have an additional table for the lists. Such a table would have a column for the id in your table and for each value in col3 -- three rows for your example row.

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Try this

select * from tbl_test where locate(concat(',',2,',') ,concat(',',c1,','))>0

Upvotes: -1

Related Questions