queryNew() joining to physical table

[DannoParker]

Can I join a query object (a query created with queryNew()) to a physical
database table? It only works if I return the physical resultset and then join
the two with a query of queries.

<cfset tempQuery = queryNew('field_1)>
<cfset temp = QueryAddRow(tempQuery)>
<cfset temp = querySetCell(tempQuery,'field_1','data',1>

<!--- this won't work --->
<cfquery datasource="this_ds" name="getrealData">
select realData from realTable, tempQuery
where realTable.realData = tempQuery.field_1
</cfquery>

<!--- but this works --->
<cfquery datasource="this_ds" name="getrealData">
select realData from realTable
</cfquery>

<cfquery name="join_tables" dbtype="query">
select *
from tempQuery,getrealData
where tempQuery.field_1= getrealData.realData
</cfquery>

Any help would be greatly appreciated!

[mxstu]

I wouldn't think so. A regular query against a physical database table takes place in the database, which knows nothing about CF query objects residing in CF memory.

[zoeski80]

No, you cannot query both the database and QoQ at the same time. The Datasource
details specified within the CFQUERY tag must apply to all the tables being
queried.

If you don't need the data out of the CF query but need to retrieve data based
on its values, you should be able to do something like this:

SELECT RealData
FROM RealTable
WHERE RealDataField IN (#ValueList(tempQuery.tempDataField)#)

But if you need to do a proper join then I think your 2nd bit of code is the
way to go - put real query into CF query and then do the join using QoQ. NB.
QoQ only supports up to 3 table joins I belive.

HTH

Zoe