Query of query issue with where clause/joins

15

Posted in ColdFusion | Posted on 07-02-2009 | 3,906 views

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.

view plain print about

1<cfquery name="q" datasource="blogdev" maxrows="5">
2select    id, title,posted
3from    tblblogentries
4order by posted desc
5</cfquery>
6<cfquery name="q2" datasource="blogdev" maxrows="5">
7select    id, title,posted
8from    tblblogentries
9order by posted asc
10</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:

view plain print about

1<cfquery name="z" dbtype="query">
2select q.posted, q.id, q.title, q2.id as qid
3from q,q2
4where q.id = q2.id
5order by q.id asc
6</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.

Comments

[Add Comment] [Subscribe to Comments]

Josh Curtiss said on 07-02-2009 at 3:52 PM #
	Whoah. What the heck is right.

Mischa said on 07-02-2009 at 4:13 PM #
	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...

Mischa said on 07-02-2009 at 4:14 PM #
	it=is

Robert Gatti said on 07-02-2009 at 4:15 PM #
	Does q2 also get changed?

Raymond Camden said on 07-02-2009 at 4:18 PM #
	Nope.

goldCoastNerd said on 07-02-2009 at 8:15 PM #
	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..

DRew said on 07-02-2009 at 9:12 PM #
	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.

Joshua Curtiss said on 07-02-2009 at 10:18 PM #
	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!

Joshua Curtiss said on 07-02-2009 at 10:19 PM #
	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!

JC said on 07-06-2009 at 4:40 PM #
	In this case, the join was the result of a SQL query and a query object built on the fly using other data.

Andy Sandefer said on 07-07-2009 at 11:38 AM #

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.

Albert said on 08-05-2009 at 8:40 AM #

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>

Raymond Camden said on 08-05-2009 at 1:15 PM #
	The simple answer is that it is not supported. Check the docs as it details which functions are supported in QoQ.

Chris Dunbar said on 06-10-2010 at 7:20 PM #
	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?

Josh said on 08-23-2010 at 12: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.

[Add Comment] [Subscribe to Comments]