Troble with EXISTS & IN queries

[Ninjaboy]

I'm trying to run NOT EXISTS or NOT IN query to find out wich records in one
table do not match the other and mysql just gives me weird error.

QUERY:

SELECT cust_id FROM customers WHERE cust_id NOT IN (SELECT * FROM
nightwatch_matr ix);
ERROR 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 * FROM nightwatch_matr ix)' at line 1

Tried this one as well

SELECT cust_id FROM customers WHERE NOT EXISTS (SELECT cust_id from
nightwatch_matr ix WHERE nightwatch_matr ix.cust_id =customers.cust _id )

ERROR 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
'EXISTS (SELECT cust_id from nightwatch_matr ix WHERE nightwatch_

Running MySQL 4.0.22 any help appriciated

[Bill Karwin]

Re: Troble with EXISTS & IN queries

Ninjaboy wrote:[color=blue]
> I'm trying to run NOT EXISTS or NOT IN query to find out wich records in one
> table do not match the other and mysql just gives me weird error.
>
> Running MySQL 4.0.22 any help appriciated[/color]

MySQL 4.0 does not support subqueries. You can either upgrade to MySQL
4.1 (which is recently released as a production-ready release), or else
rewrite your queries as joins instead of subqueries.

For example:

SELECT C.cust_id
FROM customers C LEFT OUTER JOIN nightwatch_matr ix N USING (cust_id)
WHERE N.cust_id IS NULL;

Regards,
Bill K.