Error incorrect select list on query?

Question

I have three queries:

<cfquery datasource="Intranet" name="getNotApproved">
  select submitterdept, COUNT(*) AS 'not_approved_manager'
  from [Intranet].[dbo].[CSEReduxResponses]
   where status =0 and approveddate is null
   group by submitterdept
</cfquery>

<cfquery datasource="Intranet" name="GetDepartments">
        SELECT *
        FROM CSEReduxDepts

</cfquery>
<cfquery dbtype="query" name="final_approved">
    select dep.csedept_id,DEP.csedept_name, COUNT(*) AS 'not_approved_manager'
from GetDepartments dep
join getNotApproved cs on cs.submitterdept = dep.csedept_id
where approveddate is null
group by dep.csedept_ID, dep.csedept_name
</cfquery>

When I run them on a cfm file it gives me a error:

Query Of Queries syntax error.
Encountered "\'not_approved_manager\'. Incorrect Select List, 

which is on line <cfquery dbtype="query" name="final_approved"> I know these queries work because I have run them on my microsoft sql server 2008, they show me the correct output , all three running. So I'm trying to figure out why it doesnt work on coldfusion.

Answer 1

Query of queries does not support the keyword "join". So, in addition to the other things going on, this:

from GetDepartments dep
join getNotApproved cs on cs.submitterdept = dep.csedept_id

has to become something like this:

from GetDepartments dep, getNotApproved cs 
where cs.submitterdept = dep.csedept_id

As mentioned in the comments, alias names for tables might not be supported.