MySQL Not In Not Working

Question

I am working on putting a MySQL statement together and am wondering what the problem is with it. Why doesn't this MySQL statement work?

SELECT * 
FROM  `deals` 
WHERE CATEGORY NOT 
IN  'Construction & Repair'
AND (
EXPIRE_DATE >= NOW() 
OR EXPIRE_DATE IS NULL
)
AND LOCATION =  'Melbourne'
AND STATUS =  'Active'
LIMIT 0 , 1000

I get the following error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Construction & Repair' AND ( EXPIRE_DATE >= NOW( ) OR EXPIRE_DATE IS NULL ) AND' at line 1

score 1 · Accepted Answer · answered Sep 26 '12 at 19:32

1

Put it in brackets

WHERE CATEGORY NOT 
IN  ('Construction & Repair')

answered Sep 26 '12 at 19:32

Pheonix

6,049
6
30
48

score 0 · Answer 2 · answered Sep 26 '12 at 19:32

0

I think your syntax is wrong, you want it to be:

 NOT IN ("item1", "item2")

answered Sep 26 '12 at 19:32

titania424

457
4
13

score 0 · Answer 3 · answered Sep 26 '12 at 19:33

0

Use

SELECT * 
FROM  `deals` 
WHERE CATEGORY = 'Construction & Repair'
AND (
EXPIRE_DATE >= NOW() 
OR EXPIRE_DATE IS NULL
)
AND LOCATION =  'Melbourne'
AND STATUS =  'Active'
LIMIT 0 , 1000

answered Sep 26 '12 at 19:33

Samar Rizvi

423
5
18

score 0 · Answer 4 · answered Sep 26 '12 at 19:33

0

anything after IN should be in parentheses.

IN('Construction & Repair')

answered Sep 26 '12 at 19:33

Falantar014

405
2
5
20

MySQL Not In Not Working

4 Answers4