I made a typo while posting here, the orginal query did have proper quotes. (I typed it by hand because field names, table names etc are generated dynamically by the code). The reason it was not working because MySQL supports subqueries only in versions 4.1 and up. The version I have is 4.0.14-nt.

A note for any interested readers: this syntax will only work in MySQL. I believe the ANSI standard still requires use of a sub-query, which can be rather cumbersome. Most RDBMS have their own shorthand solutions to this.