Coldfusion CFquery with Column alias

Question

I am writing a query to pull data from two table and I would like to alias a column on the query. This is generating an error on my end, just wondering if this is possible.

<cfquery name="q3" dbtype="query">
    SELECT    *
    FROM    q1, q2
    WHERE    q1.id = q2.id
</cfquery>

This works fine however if I try to do this:

<cfquery name="q3" dbtype="query">
    SELECT    q1.id as 'AppID'
    FROM    q1, q2
    WHERE    q1.id = q2.id
</cfquery>

This will generate an Invalid column index

Answer 1

Assuming you want to pull data from two database tables, you would typically use a cfquery without the keyword dbtype="query". (Using dbtype="query" is for Query of Queries --if you are running a query against the result of a previous cfquery, or a non-database query, like a cfftp directory listing.) That said, assuming your query is against database tables, using a SQL inner join works fine:

<cfquery name="q3" datasource="dbname">
    SELECT  q1.id as 'AppID', q2.id
      FROM  q1 INNER JOIN q2
               ON q1.id = q2.id
</cfquery> 

This returns only rows from both tables where q1.id = q2.id. If you wanted all of q1 and any of q2 where the ids match, you could do a LEFT JOIN instead of an INNER join. This would take all the rows from the left table and only the matching values from the right table.

<cfquery name="q3" datasource="dbname">
    SELECT  q1.id as 'AppID', q2.id
      FROM  q1 LEFT JOIN q2
               ON q2.id = q1.id
</cfquery> 

This would return every id from q1 aliased as AppID, and either the id from q2 if it matched q1.id, or else NULL if it didn't.)