>From the fine MySQL documentation: ---------- 1.4.4 Functionality Missing from MySQL
The following functionality is missing in the current version of MySQL. For a prioritized list indicating when new extensions may be added to MySQL, you should consult the online MySQL TODO list. That is the latest version of the TODO list in this manual. See section 1.6 MySQL and the future (The TODO). 1.4.4.1 Sub-selects MySQL currently only supports sub selects of the form INSERT ... SELECT ... and REPLACE ... SELECT .... You can however use the function IN() in other contexts. In many cases you can rewrite the query without a sub-select: SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); This can be re-written as: SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; The queries: SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id); Can be rewritten as: SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL ---------- -----Original Message----- From: Andrey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 09, 2002 8:12 PM To: [EMAIL PROTECTED] Subject: mysql query Hi all, What is wrong with this query: select * from MYTABLE where MYTABLE.entry NOT IN (select ANOTHERTABLE.entry from ANOTHERTABLE) mysql gives me a sintax error that looks like this: ERROR 1064: You have an error in your SQL syntax near 'select ANOTHERTABLE.entry from ANOTHERTABLE)' at line 1 does mysql support complex queries? is there any way to select something from one table that IS NOT in the other? I tried stuff like joining two tables (MYTABLE and ANOTHERTABLE) and putting conditions : MYTABLE.entry != ANOTHERTABLE.entry but that does not work for some reason. Also, keep in mind that MYTABLE.entry and ANOTHERTABLE.entry both of the type varchar(50) thanks in advance, Andrey
