Coldfusion (CFQUERY within a CFQUERY) Impossible?

Is it possible to Query within a Query in Cold Fusion by any means even a strange way like arrays or caching?

Simplest EXAMPLE of what I am trying to do.

<cfquery name="checkList" datasource="CONAM">
select PROJECTID from tblProjects
</cfquery>

<CFOUTPUT query="checkList">

<cfquery name="inside" datasource="#ProjectID#" >
select NAME from tblLEADs
</cfquery>

<CFOUTPUT query="inside">#NAME# </CFOUPUT>

</CFOUTPUT>


OBVIOUSLY the above COLD FUSION code doesn't allow me to do this strange
considering it is a QUERY="VARIABLE" which in theory should keep them
as seperate instances of each other but might be a limitation in language.

Maybe someone has another way of saving queries that doesn't cause
problems on the server? I guess someone might save the data in some
sort of array or disk method but perhaps their's something I am overlooking?
If nothing else maybe someone who used a array or disk method could post something about that or a link example?

Your code will work with the exception of your nested <cfoutput>.
To get around that, just don't specify an attribute:
<cfoutput>
and when you want to get something from the query, just say so:
#yourQuery.yourFieldName#

ColdFusion allows you to run cfqueries pretty much anywhere, with the exception of inside other cfqueries:
<cfquery name="whatever" datasource="whatever">
SELECT * FROM table
<cfquery name="yes" datasource="yes">
SELECT * FROM table2
</cfquery>
</cfquery>

That doesn't make any sense anyway, since whatever you would need to do with nested cfqueries could be accomplished through changing your SQL/query slightly.

Hope that helps.

to clarify so your saying this will work?

<cfquery name="checkList" datasource="CONAM">
select PROJECTID from tblProjects
</cfquery>

<cfquery name="inside" datasource="#checkList.ProjectID#" >
select NAME from tblLEADs
</cfquery>

<CFOUTPUT query="inside">
#NAME# </CFOUPUT>

It makes sense to me, I tried to keep things simple for the sake of getting someone to come up with a solution to my hangup, I got 150 companies that all send us data which each one has different formats and keys so it's not an option since every couple of weeks they send us new sales / customer lists to create reports on. Yeah our SQL server gets a beating and our COLDFUSION server is for displaying that info back according to projects basically each DATABASE has it's own seperate HTML Project and own tables/databases. So the only tie-in we have is our ProjectID but I was just making some tools to allow us to login directly to a script.. to try to organise easier instead of using drop down boxes and logging into each project seperately .. why? because we switch out active and inactive companies and changes to scripts all the time so as long as our main projectid is consistant the rest of those individual things don't matter and I wouldn't have to manipulate anything INOTHERwords I wouldn't have to create new LISTINGS in LOGIN screens for individual changes ..
Hence:
this is the simple explanation
<A HREF="http://192.168.0.1/#ProjectID#/script01.cfm?ID=#PRIM_KEY#">
reality each project is more like
<A HREF="http://192.168.0.1/QWEST/script01.cfm?CA_ID=#PRIM_KEY#&AgentID=#AGENT#&PHONE=#phone#&CALLCENTER=#CENTER#">
<A HREF="http://68.168.1.5/SCHOOL/index01.cfm?CA_ID=#PRIM_KEY#&AgentID=#AGENT#&PHONE=#phone#&CALLCENTER=#CENTER#">
x 150 different sites and seperate keys for every project..

that's only example's but you get the drift and each person is a
individual logged onto a script using a primary key that pulls from those
individual databases that keep track of phonetime, passwords, sales, calllogs, records and various other dialer and sales records.


EXAMPLE of what I am trying to do if that makes sense.

<cfquery name="checkList" datasource="CONAM">
select ProjectID, ProjectName, ProjectDescription, ProjectManager,
CompanyCode, ProductCode, HTMLReport, Active, CompleteGoal,
ContactsGoal, ProjectType, SPHGoal from tblProjects
ORDER BY Active DESC, ProjectID
</cfquery>

<CFOUTPUT query="checkList">

<cfquery name="inside" datasource="#ProjectID#" >
select PRIM_KEY from tableLeads
</cfquery>

<CFOUTPUT query="inside"
<A HREF="http://192.168.0.1/#ProjectID#/script01.cfm?ID=#PRIM_KEY#">
#ProjectID#-#PRIM_KEY#</a>

</CFOUTPUT></CFOUTPUT>

Maybe your right a join or something would be better, I guess the question in that would can you use two datasources inside
Coldfusion, even if possible, I doubt you can pass that query arguement from one database without a SQL procedure or something like setting a variable in SQL but I never saw
such complex SQL statements in Coldfusion as of yet so I don't know if that's possible.

[I]<cfquery name="checkList" datasource="CONAM">
select ProjectID, ProjectName, ProjectDescription, ProjectManager,
CompanyCode, ProductCode, HTMLReport, Active, CompleteGoal,
ContactsGoal, ProjectType, SPHGoal from tblProjects
ORDER BY Active DESC, ProjectID
</cfquery>

<CFOUTPUT query="checkList">

<cfquery name="inside" datasource="CONAM">
select *, PRIM_KEY from tableLeads where PRIM_KEY ="#ProjectID#"
</cfquery>


<A HREF="http://192.168.0.1/#checklist.ProjectID#/script01.cfm?ID=#inside.PRIM_KEY#">
#checklist.ProjectID#-#inside.PRIM_KEY#</a>

</CFOUTPUT>


A join in checklist would work as well. I do this all the time, but I may have crossed some of your code up. Hope this helps.

You can't call tables in multiple datasources from a single query, but in Oracle you CAN call tables in different schemas. Depending on your DBMS you may be able to do this too...it basically means prefixing your SQL elements with the schema/database name, something like this:

select e.firstName, u.userName
from schema1.employees e,
schema2.users u
where e.employeeID = u.userID
and e.employeeID = #val( form.employeeID )#

Obviously replace "schema1" and "schema2" with the actual schema names that you want to call. Note that the user account you're using to run the cfquery must have at least select access on the tables in both schemas for this to work.

As a side note, whenever you are running a query and then looping over it and running other queries, that is a big red flag that you could probably do that in one query using joins or aggregate functions. SQL is easy to do basic things with, but it takes a very very long time to get really good at SQL. Becoming an advanced SQL developer is one of the best things you can do for your career because whatever platform or programming language you use, you will almost always have to use SQL.

You can easily perform a query on a query,heres how it goes....

<!---Gets raw data--->
<CFQUERY DATASOURCE="OWS" NAME="GetAllContacts">
SELECT *
FROM Contacts
</CFQUERY>

<!---Queries GetAllContacts quert--->
<CFQUERY NAME="GetMailingList" DBTYPE="Query">
SELECT LastName,FirstName,MailingList
FROM GetAllContacts
WHERE MailingList=1
</CFQUERY>

<!--- Notice I didnt quote a datasource in the second <CFQUERY> tag. I cited DBTYPE="Query" telling CFMX that what to follow was based on a query. Then the table I quoted was "FROM GetAllContacts" which was the title of the previous query. See its easy !!!--->

You sure can do a query of queries. But there are lots of random problems with query of queries when it comes to data types (CF tries to cast everything to a Java type and it isn't always what you intend). But the point still remains that in general, if you're doing multiple queries or query of queries, in many cases you could actually do the same thing in a single SQL statement.

The simplest way to do this is to add a cfinclude

Here's the example:

<cfquery name="checkList" datasource="CONAM">
select PROJECTID from tblProjects
</cfquery>

<CFOUTPUT query="checkList">

<cfinclute template="insidequery.cfm">

</CFOUTPUT>

------
and inside the insidequery.cfm:

<cfquery name="inside" datasource="#ProjectID#" >
select NAME from tblLEADs
</cfquery>

<CFOUTPUT query="inside">#NAME# </CFOUPUT>


-----
That way you'll get a cfoutput inside a cfoutput

Hope this help you

Steve

Might be simple, but it's probably not preferred. Like I said, when you are running one query and then looping over it and running other queries, that is an indicator that you are taking the wrong approach. These can almost always be done in one query using JOINs.