Help with simple MYSQL Query

Hi i'm getting a syntax error for a query i am trying to run directly onto the database (so this is not in my php code). The query i am running is:

SELECT * FROM table1 WHERE fkid1 NOT IN
(
SELECT pkid FROM table2
)

fkid1 and pkid are both fields in the the respective tables of exactly the same type

I haven't had errors running this type of query on any other types of databases (oracle, sql server etc) and i can run it fine on another php/mysql server. It also errors if I use IN rather than NOT IN but does work if i replace the nested select statement with hard coded values.

Is it possible that a version of mysql does not allow this? Anyone got any ideas on what might be causing the problem.

Oh and the error is:

#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 'select pkid from table2 ) LIMIT 0, 30' at line 1

 

The query you mentioned does not contain LIMIT but the error statement does...

 

Which mysql version you are using? And what do you mean by hard coded values?

 

hi yeah sorry i just took the limit stuff off the query because that is what phpmyadmin put on automatically.

When i say hardcoded i mean for example:

select * from table1 where fk1 NOT IN (1,2,3,4,5)

Thanks

 

Sorry forgot to mention, mysql version is: 5.0.32

 

The query looks fine. Try LEFT JOIN instead

SELECT t1.* FROM table1 t1 
LEFT JOIN table2 t2 ON t1.fkid1 = t2.pkid
WHERE t1.fkid IS NULL

Code (mysql):

 

ah didnt think of that, yeah that works fine thanks. Strange though.

 

Can you make a simple SELECT to if this is returning valid results

SELECT pkid FROM table2

Code (markup):

 

yeah i can, both selects work and return results. :\