Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
CF 5 - Querying a query 2
- Thread starter Zooo
- Start date
-
1
- #2
What do you mean with querying a query?
If you want to have a "nested" query you
can always use a <CFINCLUDE> tag.
You insert the CFINCLUDE on the template
of the first query and on the "included"
template you write your second query.
Your second query can use the variables
of the first template.
Is this understandable?
Else explain the problem with an example please.
SU
VETO
If you want to have a "nested" query you
can always use a <CFINCLUDE> tag.
You insert the CFINCLUDE on the template
of the first query and on the "included"
template you write your second query.
Your second query can use the variables
of the first template.
Is this understandable?
Else explain the problem with an example please.
SU
VETO
Yeah, I have. It's pretty nice - and very easy.
Run a regular database query, e.g.:
<cfquery name="myQuery" datasource="#ds#">
SELECT fname, email FROM people
WHERE gorgons = 3
</cfquery>
Then you can treat the resulting recordset just
like a data table in a later query (or several),
like:
<cfquery name="aolSearch" dbtype="query">
SELECT fname FROM myQuery
WHERE email LIKE '%@aol.com'
</cfquery>
That's all there is to it. It's just a matter of
adding the dbtype attribute to the second query,
rather than the datasource attribute, and then
being sure to reference the earlier query in the
SELECT statement of the second one.
It's actually pretty damn handy, even though this
goofy example would be a poor use of the feature.
Run a regular database query, e.g.:
<cfquery name="myQuery" datasource="#ds#">
SELECT fname, email FROM people
WHERE gorgons = 3
</cfquery>
Then you can treat the resulting recordset just
like a data table in a later query (or several),
like:
<cfquery name="aolSearch" dbtype="query">
SELECT fname FROM myQuery
WHERE email LIKE '%@aol.com'
</cfquery>
That's all there is to it. It's just a matter of
adding the dbtype attribute to the second query,
rather than the datasource attribute, and then
being sure to reference the earlier query in the
SELECT statement of the second one.
It's actually pretty damn handy, even though this
goofy example would be a poor use of the feature.
-
1
- #5
strantheman
Programmer
Query of queries baby!
This works only in CF 5.0 but it works! The first two queries are from two different databases. The third queries the results of the two and joins on the origin.
Query of queries type functionality can be accomplished using SQL server or other DBMS, but CF will let you combine ANY type of query including LDAP queries (CFLDAP), email queries (CFPOP), or even Verity collections using CFSEARCH.
This is just an example.
<!--- access --->
<CFQUERY name="getApples" datasource="ds1">
select color,weight,origin from table_apples
</CFQUERY>
<!--- oracle --->
<CFQUERY name="getOranges" datasource="ds2">
select circumference,weight,origin from table_oranges
</CFQUERY>
<!--- get a combined record set filtered from the two previous record sets --->
<CFQUERY name="combined" datasource="query">
select
a.weight
, a.origin
, o.weight
, o.origin
from getApples a, getOranges o
where a.origin = o.origin
</CFQUERY>
This works only in CF 5.0 but it works! The first two queries are from two different databases. The third queries the results of the two and joins on the origin.
Query of queries type functionality can be accomplished using SQL server or other DBMS, but CF will let you combine ANY type of query including LDAP queries (CFLDAP), email queries (CFPOP), or even Verity collections using CFSEARCH.
This is just an example.
<!--- access --->
<CFQUERY name="getApples" datasource="ds1">
select color,weight,origin from table_apples
</CFQUERY>
<!--- oracle --->
<CFQUERY name="getOranges" datasource="ds2">
select circumference,weight,origin from table_oranges
</CFQUERY>
<!--- get a combined record set filtered from the two previous record sets --->
<CFQUERY name="combined" datasource="query">
select
a.weight
, a.origin
, o.weight
, o.origin
from getApples a, getOranges o
where a.origin = o.origin
</CFQUERY>
- Status
