"Not In" query fails
mysql
Solution
The problem can be one, or more of the following situations:
- foo is not a table
- bar is not a column from foo table
- foobar is not a table
- bar is not a column from foobar table
- bar from foo table and bar from foobar have different (not compatible) datatypes
It's possibly a BUG from MYSQL4.0 (check HERE for that info).
Use `NOT EXISTS` instead of `NOT IN` like this:
select *
from foo
where bar NOT EXISTS (select bar from foobar WHERE foobar.bar = foo.bar)
Problem
pulling some info from a MySQL db via openquery: ``` select * from foo where bar not in (select bar from foobar) ``` Now, if I replace the subquery with hardwired numbers, it works fine, but i have near a 1000 numbers i need to exclude. I haven't been able to figure this out; both queries run fine by themselves. the error always tells me I have an syntax error in 'select bar from foobar)' thanks Edit: here is the error: ``` [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-log]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 'select bar from foobar)' at line 3". ```