Query within a query

[lennartr]

I would like to know if its possible to join the resultset of two previous querys in one query within a query.
It seems to work fine to loop over one query and join with the other one record at a time but sometimes it would bee nice to bee able to do something like this:

<cfquery name=&quot;q1&quot; datasource=&quot;source1&quot;>
SELECT a,b FROM tab1
</cfquery>

<cfquery name=&quot;q2&quot; datasource=&quot;source2&quot;>
SELECT c,d FROM tab2
</cfquery>

<cfquery name=&quot;finalquery&quot; dbtype=&quot;query&quot;>
SELECT x.b,y.d
FROM q1 x, q2 y
WHERE x.a = y.c
</cfquery>

When trying this I get a
PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag
error

Or do I have my head uma now?

 

[Silvano]

I am not quite sure what you are trying to accomplish, so I am just going to give you few cfquery examples and ways you can work with multiple tables;

if you want to select two different field from two tables, you can consider following example:

<cfquery name=&quot;finalquery&quot; datasource=&quot;dbn&quot; dbtype=&quot;ODBC&quot;>
SELECT table_1.field_1, table_2.field_1
FROM table_1, table_2
WHERE table_1.field_1 = 'someValue' AND table_2.field_1 = 'someValue'
</cfquery>

you can also make inner join (also available: Equi-join, Natural Join, Left Outer Join, Right Outer Join, Full Outer Join, Cross Join, Self Join):

<cfquery name=&quot;finalquery&quot; datasource=&quot;dbn&quot; dbtype=&quot;ODBC&quot;>
SELECT table_1.field_1, table_2.field_1
FROM table_1 INNER JOIN table_2
ON table_1.field_ID = table_2.field_ID
WHERE (table_1.field_1 LIKE '%Val')
</cfquery>

in any query it is possible to write sub query:

<cfquery name=&quot;finalquery&quot; datasource=&quot;dbn&quot; dbtype=&quot;ODBC&quot;>
SELECT table_1.field_1, table_2.field_1
FROM table_1, table_2
WHERE table_1.field_1 = 'someValue' AND table_2.field_1 = (SELECT table_1.field_1
FROM table_1
WHERE table_1.field_1 <> 500)
</cfquery>

in case you have same fields in multiple tables, you combine results using &quot;UNION&quot; operator (also available: UNION [ALL], MINUS, INTERSECT):

<cfquery name=&quot;finalquery&quot; datasource=&quot;dbn&quot; dbtype=&quot;ODBC&quot;>
SELECT field_1
FROM table_1
WHERE field_1 = 'someValue'
UNION
SELECT field_1
FROM table_2
WHERE field_1 = 'someValue'
</cfquery>

analize your situation and decide what kind of query you can apply; then, we'll work out the details...



Sylvano
dsylvano@hotmail.com

 

[lennartr]

OK, I'm sorry for not beeing clear enough about the problem. Joining tables (outer or inner) from ONE datasource isn't much of a problem but what if you want to join data from TWO or more datasources in ONE query?

My colleges and I have been reading discriptions of the new features in CF5 and on some of these pages on the web it would seem that CF can do just this.

What I realy would like to do is something like this:

<cfquery name=&quot;q&quot; >
SELECT s1.a, s1.b
FROM SOURCE1.tab1 s1, SOURCE1.tab1 s2
WHERE s1.x = s2.y
</cfquery>

I have tried using a syntax like the one in the original question making two basequerys and then joining them in
a CFQUERY with DBTYPE=QUERY (and no datasource) but CF-server seems to allow only one &quot;previus&quot; query in the SQL FROM clause, but that works ok.

LennartR

 

[strantheman]

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.

Let me know what specifically you're trying to do. This is just an example.

<!--- access --->
<CFQUERY name=&quot;getApples&quot; datasource=&quot;ds1&quot;>
select color,weight,origin from table_apples
</CFQUERY>

<!--- oracle --->
<CFQUERY name=&quot;getOranges&quot; datasource=&quot;ds2&quot;>
select circumference,weight,origin from table_oranges
</CFQUERY>

<!--- get a combined record set filtered from the two previous record sets --->
<CFQUERY name=&quot;combined&quot; dbtype=&quot;query&quot;>
select
a.weight
, a.origin
, o.weight
, o.origin
from getApples a, getOranges o
where a.origin = o.origin
</CFQUERY>

 

[lennartr]

Thanks!

When I got my act together it worked just fine,
One remark though:

It seems that this syntax works ok

<CFQUERY name=&quot;combined&quot; dbtype=&quot;query&quot;>
select
getApples.weight as output1
, getApples.origin as output2
, getOranges.weight as output3
, getOranges.origin as output4
from getApples , getOranges
where getApples.origin = getOranges.origin
</CFQUERY>

And this doesn't:

<CFQUERY name=&quot;combined&quot; dbtype=&quot;query&quot;>
select
a.weight
, a.origin
, o.weight
, o.origin
from getApples a, getOranges o
where a.origin = o.origin
</CFQUERY>

Have you tried?