Creating a temporary table with CROSS JOIN

Question

I'm trying to create a temporary table for application data I've got. When I run the sub query it works fine but when I run it within the temporary table parameters it fails with

Error Code: 1054. Unknown column 'getApplicationsForRespID.app_id' in 'where clause'

CREATE TEMPORARY TABLE CurrentApplications ENGINE=MEMORY AS 
(
SELECT *, planning_scheme.markus_ra, planning_scheme.metro_or_rural
FROM application
INNER JOIN key_table ON key_table.app_id = application.app_id
INNER JOIN planning_scheme ON planning_scheme.ps_code = application.planning_scheme

CROSS JOIN 
(
    SELECT key_table.app_id AS app_id2, planning_return.resp_authority AS resp_id
    FROM key_table
    INNER JOIN planning_return ON key_table.return_id = planning_return.return_id

)getApplicationsForRespID

WHERE application.app_id = getApplicationsForRespID.app_id
AND key_table.is_current = 1

)

Answer 1

In your cross join subquery, you have key_table.app_id AS app_id2, which means, that no field getApplicationsForRespID.app_id exists.

Use getApplicationsForRespID.app_id2 in the WHERE clause instead!