Are there join restrictions on Recordsets or something?

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