Subqueries

[Phil Haigh]

I feel a bit daft posting this as I'm sure there must be an obvious answer out there somewhere. Anyway, here goes...

I can't get subqueries to work - at all! I started off with a problem in this SQL statement:

SELECT * FROM Driver WHERE Driver_Id = (SELECT DISTINCT Driver_Id FROM Race_Entry WHERE Race_Id = (SELECT Race_ID FROM Race WHERE Season=?)) ORDER BY Surname, First_Name

The ? is a parameter bound at runtime. However I get the following message:

"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 DISTINCT Driver_Id FROM Race_Entry WHERE Race_Id = (SELE"

I've double-checked the query syntax and can't see any problems with it. Running interactively in mysql produces the same result.

I've also tried even simpler subqueries and all return the same error. Basically, all subqueries are thrown out as invalid syntax. Yet my understanding is that Mysql 4.1.x supports subqueries.

What am I doing - or not doing?

BTW I'm running Mysql 4.1.20 on Windows XP with version 3.0.14 (production) of the JDBC driver.