subqueries in query of queries

[helenmhudson]

I've been looking at this for a while so I might be punch drunk.

I have a query of queries on a mySQL sales database. I want to display
columns across for the monthly sales values - client names on the Y axis,
monthly sales on the Y axis... pretty common situation.

I've been working on a solution which merges several
SELECT
statements (one per month where only the month I'm interested in has values
and the other is 0 ) and then all unioned with
UNION ALL
and then, around all that, I have a summing statement, adding them all
together.

I'm trying to do this now in a cfm file and the query of queries is rebelling
at the first
(
(when I'm about to get into my subquery).

My questions are:
1. can query of queries support subqueries (perhaps my syntax is wrong)
2. anyone have a better way of displaying montly values on a summing database

Any advice much appreciated!

Helen

[Dan Bracuk]

I don't think QofQ supports subqueries in the from clause which is what you need. An alternative approach would be to loop through your query and create a 2D array.

[LL@Work]

You could try this approach:

First, split out the subquery into its own query:

<cfquery name="mySubquery" dbtype="query">
select myfields from myquery
</cfquery>

Then, store the results into a quoted list:

<cfset MyList = QuotedValueList(mysubquery.myfields)>

Then, use the resulting list in the WHERE clause of your main query (be sure
to use the PreserveSingleQuotes function):

<cfquery name="myfinalQuery" dbtype="query">
Select allMyFields
from myOtherQuery
Where myfield NOT IN (#PreserveSingleQuotes(MyList)#)
</cfquery>