"Not In" query fails

Question

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".

Can you show the actual query you are using not one replaced with 'foo' and 'foobar' — Jared, May 23 '12 at 16:06
Nothing really wrong with that query setup, unless your non-foo/barred fields are reserved words. — Marc B, May 23 '12 at 16:07
Works just fine: http://www.sqlfiddle.com/#!2/80d42/1. What do you mean by "via openquery"? — mellamokb, May 23 '12 at 16:09
openquery is an SQL Server function that lets you link to and query other DBs. — Limey, May 23 '12 at 16:14

aF. · Accepted Answer · 2012-05-23T16:42:01.707

3

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)

edited May 23 '12 at 16:42

answered May 23 '12 at 16:11

aF.

64,980
43
135
198

hmmm....it could be number 5. I don't have the greatest access to the box, so I don't know how the columns were setup datatype wise. I will dig into this. – Limey May 23 '12 at 16:18
If it works when you put numbers, bar in foo table must be a number datatype and possibly it's not in bar from foobar table. – aF. May 23 '12 at 16:24
I checked both columns were numbers by attempting math on them, both allowed for propeer math (both interger and float) to be done on them – Limey May 23 '12 at 16:32
From more searching I have been doing, it looks like this is an issue with openquery on the SQL Server side, which I should of guess right off after some basic syntax was erroring. – Limey May 23 '12 at 16:39
@Limey check my edit and try what I've put. Prolly should work as a fix for now. – aF. May 23 '12 at 16:42

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

Are there any NULLs in your foobar subquery? This would mess up your approach. If so, more details at SQL "select where not in subquery" returns no results

edited May 23 '17 at 12:20

Community

1
1

answered May 23 '12 at 16:11

William Entriken

37,208
23
149
195

no nulls, selecting a primary key, but i double checked, jus to make sure. :) – Limey May 23 '12 at 16:16

"Not In" query fails

2 Answers2