CODEWITHSUNDEEP
phpmysql
Yung Fei
Yung Fei

Reputation: 75

mysql query "not in"

I have a query 

SELECT * FROM `photo` p where 4 not in (2,4,5)

from the query it is not show anything because there is 4. but why if we query 

SELECT * FROM `photo` p where 4 not in (user_view)

table photo:

photo ID | user_view
1        | 2,3,4,5

it is still show the record. why the code is not running well if we use not in from the database field?

how to make the same function with 

SELECT * FROM `photo` p where 4 not in (2,4,5)

I want to show the record only if the photo is haven't see by user. So it will show uniquely by user who don't have an ID in user_view.thanks

Upvotes: 1

Views: 48

Answers (2)

spencer7593
spencer7593

Reputation: 108380

A column storing a comma separated list is a single value. It's just a single string value that happens to contain comma characters.

Those commas are not considered to be part of the SQL text.

When you do this is a: 

  foo NOT IN ( 2 , 4 , 5 )

MySQL sees that as three separate values, the commas are part of the SQL text; that's equivalent to 

  ( foo <> 2 AND foo <> 4 AND foo <> 5 )

When you do this:

  foo NOT IN ( '2,4,5' )

That's equivalent to: 

  foo <> '2,4,5'

(In MySQL, in a numeric context, the string literal is going to evaluate as a numeric value of 2.)

If you are asking how to "fix" this without changing your database (and storing comma separated lists is a SQL anti-pattern)

You could do this:

SELECT p.* FROM `photo` p 
 WHERE NOT FIND_IN_SET(4,p.user_view)

Or, you could do this:

SELECT p.* FROM `photo` p 
  WHERE CONCAT(',',p.user_view,',') NOT LIKE '%,4,%'

Upvotes: 0

kellanburket
kellanburket

Reputation: 12833

If the column user_view is a comma delimited list like it seems to be, you can use the built-in FIND_IN_SET function:

 SELECT * FROM `photo` p where FIND_IN_SET(4, `user_view`) != 0

This will check if the input '4' exists as one of the items in the user_view column and return the index number if found and 0 if not.

Upvotes: 1

Related Questions