Are there join restrictions on Recordsets or something?

[davidbarrackphoto]

I have the following recordset that runs just fine when testing within a recordset and also just fine when running as a query in phpMyAdmin.  However as soon as I try to create a dynamic table I get the following error that is displayed within the page in the design view: 

MM_ERROR: MySQL 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 ') AS dt ON (PRV_Reservation_People.PRV_room_name = dt.PRV_room_name) WHERE PRV_r' at line 2

Here is the SQL:

SELECT PRV_Reservation_People.PRV_person_first_name, dt.cnt FROM PRV_Reservation_People

JOIN (

        SELECT PRV_room_name, COUNT(*) AS cnt

        FROM PRV_Reservation_People

        GROUP BY PRV_room_name )

        AS dt

ON (PRV_Reservation_People.PRV_room_name = dt.PRV_room_name) WHERE PRV_reservation_ID = colname

I thought it had something to do with the where clause but changed colname to a constant and had the same problem.   I am doing all testing against the same remote database.  So I am guessing the DW is mangling the SQL somehow. 

I appreciate the help.

David

[David_Powers]

You are using a subselect, which were not supported by MySQL until version 4.1. Is the remote database MySQL 4.1 or above?

You can also check what DW is doing to the SQL query by using echo to display the dynamically generated value.