Query of query issue with where clause/joins

This post is more than 2 years old.

A user reported this to me earlier in the week. I was sure he was wrong until I confirmed it myself. Imagine you have 2 queries you want to join using a query of query. Here is a quick sample.

<cfquery name="q" datasource="blogdev" maxrows="5"> select id, title,posted from tblblogentries order by posted desc </cfquery> <cfquery name="q2" datasource="blogdev" maxrows="5"> select id, title,posted from tblblogentries order by posted asc </cfquery>

Admittedly, this is kind of a dumb example, but I wanted to keep it simple. q is a query sorted by posted, descending, and q2 is the reverse of that. To join with query of queries, you must use a where clause, you can't use join. Here is the QofQ I used:

<cfquery name="z" dbtype="query"> select q.posted, q.id, q.title, q2.id as qid from q,q2 where q.id = q2.id order by q.id asc </cfquery>

Note that I gave the new query the name z. Everything should be kosher, right? Well watch what happens when I dump q before and after the query of query.

What the heck, right? The error goes away if you make a duplicate of q and use that in the query of query.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Josh Curtiss posted on 7/3/2009 at 12:52 AM

Whoah. What the heck is right.

Comment 2 by Mischa posted on 7/3/2009 at 1:13 AM

I remember an IBM rep telling me once that the DB2 engine on their AS/400 was still getting updates and fixes regularly because "customers always seem to be able to write a query that throws an error". And that it one of the oldest SQL engines out there...

Comment 3 by Mischa posted on 7/3/2009 at 1:14 AM

it=is

Comment 4 by Robert Gatti posted on 7/3/2009 at 1:15 AM

Does q2 also get changed?

Comment 5 by Raymond Camden posted on 7/3/2009 at 1:18 AM

Nope.

Comment 6 by goldCoastNerd posted on 7/3/2009 at 5:15 AM

hah, if i had a doller for everytime Q of Q acted in a totally weird way, i'd be pretty rich by nw... i try not to use Q of Q unless i have to , prefer doing everything in SQL..

Comment 7 by DRew posted on 7/3/2009 at 6:12 AM

ditto goldcoastNerd. The lack of documented syntax, no joins or aliasing, and speed has made me prefer to use complex db queries rather and qoq's.

Comment 8 by Joshua Curtiss posted on 7/3/2009 at 7:18 AM

In general, I'd say it's better to write joins in a db query than doing q of q. ColdFusion's specialty isn't database processing. :-D But sometimes tables can't be joined or other scenarios exist (working with non-db data) where query of queries is so handy!

Comment 9 by Joshua Curtiss posted on 7/3/2009 at 7:19 AM

In general, I'd say it's better to write joins in a db query than doing q of q. ColdFusion's specialty isn't database processing. :-D But sometimes tables can't be joined or other scenarios exist (working with non-db data) where query of queries is so handy!

Comment 10 by JC posted on 7/7/2009 at 1:40 AM

In this case, the join was the result of a SQL query and a query object built on the fly using other data.

Comment 11 by Andy Sandefer posted on 7/7/2009 at 8:38 PM

Not to sound too absolutest (that's probably not a real word ;)) but I have a general rule about when to use a query of query. If I'm going after table data in a database I flat out will avoid using query of query like the plague. However, it does have its place when you need to perform complex querying on queryable (again - not a real word! ;)) non-table data. Query of query can totally save you when you need to query data returned from a cfdirectory call or data that you've retrieved from an exchange server. Complex file handling and e-mail data is a good place to start using query of query - just some food for thought.

Comment 12 by Albert posted on 8/5/2009 at 5:40 PM

I have another issue with QofQ.
I am trying to use string functions in QofQ like LEFT(string, count), or RIGHT(string, count) and I have:
" Query Of Queries syntax error.
Encountered "Left. Incorrect Select List, Incorrect select column".
For first query I am using a :
<cfdirectory directory="#application.installPath##application.PDFfilesDir#" name="memberList" action="LIST" sort = "directory ASC">
Then,
<cfquery dbtype="query" name="reportsQuery">
SELECT distinct Name, Left(Name, 12) AS PDF
FROM memberList
</cfquery>
I need to show only first 12 charachters in the <cfselect>

Comment 13 by Raymond Camden posted on 8/5/2009 at 10:15 PM

The simple answer is that it is not supported. Check the docs as it details which functions are supported in QoQ.

Comment 14 by Chris Dunbar posted on 6/11/2010 at 4:20 AM

I know this is a bit old now, but with regards to Albert's comment, is there a simple way to accomplish selecting a distinct substring from a cfquery result set?

Comment 15 by Josh posted on 8/23/2010 at 9:57 PM

i found a pretty easy workaround for the left/right substring selection was to just do all the substring selections in the previous query that you are selecting from, alias it, and in the QoQ, you can just select whatever column you aliased in the previous query.