Compare a single value column against multiple possible values

[Errant_Shadow]

First, I don't need to search a set for a single value. I actually need to do the opposite.

 

I'm filtering my query for a single field that has a single numerical value. This field references another table by it's primary key.

 

table1:
field = value -- references table2.id

table2:
id
data

 

basically, I want results when table1.field is not equal to certain values. Let's say for the ids 1-5, I only want results if the value is NOT 2 or 3.

 

How would I do that without a bunch of where clauses (if possible)?

 

This is how I'm doing it right now:

SELECT
`table1`.*
FROM
`table1`, `table2`
WHERE
`table2`.`id` != '2'
OR
`table2`.`id` != '3'

 

and that works fine... unless I want to filter out like 50 values. Yes, I could do a loop, but I was hoping there was a simpler way.

[awjudd]

Have you tried to use the 'BETWEEN' operator?

 

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

 

Basically you want everything that isn't between a set of numbers?

 

~juddster

[Errant_Shadow]

That'll definitely work for comparison to sequential numerical values, thank you.

 

I'm trying to wrap my head around IN...

 

Am I correct in understanding that the IN operator will return the requested field only if it's value is found in the series of values given?

 

such as this:

SELECT `myField` IN (0, 1, 4, 5)

 

Where if myField is 1, then it will return it, but if myField is 3, it wont? Or am I completely misunderstanding this?

[fenway]

IN() is just basically shorthand for a series of OR comparisons -- which explains why it doesn't work with NULL.

[brianlange]

You can use the Logical Operator "Not" before "in"

 

Select `myfield` From `table` where `myfield` not in (2,3)

[fenway]

Of course, that's horrible for index usage.

[Errant_Shadow]

Of course, that's horrible for index usage.

 

It sounds like it's work great for what I need, though I am using it to compare foreign keys. Would this be ill-advised of me to do?

[fenway]

It just means to have to scan O**2 rows.