Query of query issue with where clause/joins

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 developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

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

Comments