Lee esta página:
http://dev.mysql.com/tech-resources/...es_part_1.html
Aqui un párrafo. Dices que tienes 4.0...., según la página se requiere la versión 4.1...., me parece que no soporta sub-consultas...
Cita: In order to use subqueries in MySQL you will need a version of MySQL 4.1. Any version would be OK, but as 4.1 is alpha, you'll definitely want the latest. So, go and grab the newest 4.1 and get it installed before moving on... or just be a spectator and read the examples