inner joins on a query-of-query?

[deepatpaul]

I'm trying to use aliases for a query-of-query, and I'm getting a error that I presume tells me I can only perform a q-of-q against the original recordset, and nothing more. Here's my code:

<cfquery name="qSearch" dbtype="query">
SELECT s.*, l.locationName
FROM qInitialSearch s, tblLocations l
WHERE s.workshopCounty = l.locationID
ORDER BY l.locationName ASC,
s.workshopCity ASC,
s.workshopType ASC,
substring(s.workshopStart,7,4) DESC,
substring(s.workshopStart,4,2) DESC,
substring(s.workshopStart,1,2) DESC
</cfquery>

And this is the error I get:

Query Of Queries syntax error.
Encountered "s" at line 0, column 0.

The query it's calling from is relevant, unless I'm missing an obvious piece of syntax here.

 

[r937]

Note: ColdFusion does not support aliases for table names (see Query of Queries user guide)

but beyond that, i don't think you can run a query that tries to join a cached query result with another database table

it looks like tblLocations is a table, so you will ahve to bring it into cf with its own query -- and then you can join (note: using table list syntax, not JOIN syntax) the two query results

rudy

http://r937.com/

SQL Consulting