Page 1 of 1

SQL query excluding something

Posted: Thu Jun 10, 2004 10:40 am
by Bill H
I discovered this by accident and have been unable to verify it in documentation, so perhaps somone here can do so. It seems to work, but...

I have an integer column which may contain values between 1 and 6 and I have been excluding records with a value of 2 with the following SQL statement:

Code: Select all

SELECT * FROM  Table WHERE (Col<2 OR Col>2) AND...
That's fine when it's the only condition, but becomes a bit awkward when it's part of a much (much) longer statement. This seems to do the same thing, but is it valid as part of a larger statement and can I count on it?

Code: Select all

SELECT * FROM  Table WHERE Col-2 AND...
Sorry, using MySQL.
:?:

Posted: Thu Jun 10, 2004 10:47 am
by feyd
SELECT * FROM table WHERE Col NOT '2'

?

Posted: Thu Jun 10, 2004 5:19 pm
by Bill H
OK, thanks.

Edit @ 4:09PDT The "Col NOT 2" syntax produces a MySQL error. When I change it to "Col-2" the query executes properly.

I cannot find anything in MySQL documentation for NOT or the minus sign in the SELECT syntax, so I'm a bit reluctant to use the "Col-2" syntax despite its convenience. The "Col NOT 2" does not work, with or without the single quotes around the value. (It should not need them since the column type is an integer.)
:?

Posted: Thu Jun 10, 2004 8:43 pm
by feyd
hmm.. I could have swore that worked in the past for me.. :?