CODEWITHSUNDEEP
mysql
Paris Char
Paris Char

Reputation: 497

NOT IN() returns weird result in mysql

I have this mysql statement:

SELECT  `review_userid` ,  `book_id` ,  `review_id` ,  `user_class` , COUNT( review_id ) 
FROM  `booklist_books` 
LEFT JOIN  `booklist_reviews` ON booklist_books.book_id = booklist_reviews.review_bookid
LEFT JOIN  `e107_user` ON booklist_reviews.review_userid = e107_user.user_id
WHERE '12' NOT IN (`user_class`)
GROUP BY  `review_userid` 
ORDER BY COUNT(`review_id`) DESC 
LIMIT 0 , 100

user_class is a comma-delimited string (e.g: 1,2,3,4,5), as you can see below.

Using phpmyadmin, I get the following result:

mysql result

Why do I get results that have '12' in the user_class field since I am using '12' NOT IN(... ??

If I can't user user_class in the IN() operator, what is the alternative to this?

Thanks

Upvotes: 1

Views: 121

Answers (3)

xdazz
xdazz

Reputation: 160833

IN or NOT IN is not working like that, check the function FIND_IN_SET

WHERE FIND_IN_SET('12', `user_class`) = 0

Upvotes: 3

Wiseguy
Wiseguy

Reputation: 20873

value IN (...) checks to see if value is one of the items listed in (...). In your case, the list only contains one value: the value of the user_class column. The string '12' is not equal to the string '3,6,9,12', so it finds no match. IN is not searching for matching substrings in the column's value.

Because you have multiple comma-separated values in one field, you'd want to employ FIND_IN_SET().

If you have the option of restructuring the data, you should employ database normalization to avoid this mess of having multiple values in one field.

Upvotes: 1

Kermit
Kermit

Reputation: 34055

Syntax is expr IN (value,...)

WHERE `user_class` NOT IN ('12')

If two or more tables use the same column name, it is ambiguous and you should need to prefix or nick name your tables to clearly define which column you are calling.

WHERE table.column NOT IN ('12')

Nick name:

SELECT table a, table2 b

Upvotes: 1

Related Questions