Can anyone tell me why this query of queries doesn't work. I know it has something to do with the two tables but CF documentation says that you can use it with two tables.

<cfquery name="pledgemaster" datasource="mydatabase" maxrows="10">
SELECT *
FROM PaymentTable, studenttable
WHERE
</cfquery>

<cfquery name="viewpledges2" dbtype="query" maxrows="10">
SELECT PaymentTable.StudentID, PaymentTable.PledgeAmount, PaymentTable.DateAdded, studenttable.StudentID, studenttable.ClassID, studenttable.FirstName, studenttable.LastName, SUM(PaymentTable.PledgeAmount) AS Total_PledgeAmount, SUM(PaymentTable.PledgeAmount) AS GrandTotal_PledgeAmount

FROM pledgemaster

WHERE PaymentTable.StudentID = studenttable.StudentID
AND studenttable.SchoolID =
<cfqueryparam value="#URL.SchoolID#" cfsqltype="cf_sql_numeric">
AND PaymentTable.Remove = 0
GROUP BY studenttable.ClassID
ORDER BY GrandTotal_PledgeAmount DESC
</cfquery>

Can you Post the error message you are getting?

First Off, not sure if it's a typo, but you have nothing after your WHERE clause. You should remove it if you are not filtering your main query.

It's also good practice not to do Select all (*) You should specify every table column you want to use. You don't have to use the full table name either. You're first query could look like this.:

Yeah, that where is just a typo. The coldfusion documentation suggestions making a master query that selects ALL columns. And then, they recommend using queries of queries. They say this is better than having a whole bunch of different queries on one page, which is what I have now.

The error I get says something like:
"column PaymentTable.StudentID cannot be found.

The problem is that PaymentTable is not a column, it's a table. I figured by doing the select * in my master query, it would include everything. But, what you are telling me is that I have specify each column in the master query. I can't do a select ALL?

Thanks for any additional help. Also, is it really bad to have many queries on one page instead of the query of queries?

windsorben - I'm not sure I understand the point of doing this. You're doing a SELECT * with no WHERE conditions, and then filtering that query with WHERE conditions? Wouldn't it make more sense to just write one query? Are you using any of the non-filtered data?

I need to produce a bunch of flash charts that use different GROUP BY conditions. To my knowledge, you can't specify more than one GROUP BY in a query. Therefore, I was going to do one master query to pull up everything I need and then do a bunch of query of queries to make all the different GROUP BYs. According to Macromedia's documentation, this is superior to many seperate queries. It keeps the database from being hit for each different GROUP BY. Let me know if you know of a better solution.

Thanks!

Not sure how much better that would be. If all the graphs are in one place can you loop over it and perform a lot of smaller queries and dynamically update the 'group by' statement?

I'm just a beginner. I haven't figured out that whole loop thing yet. Thanks for the suggestion. I guess I'll have to pull out Forta's giant book and figure out the loop process.

Look at the various possibilities with CFLOOP, particularly those involving looping over the results of a query.

Don't know if anyone will ever read this far, or if this is the correct solution, but two things got this query of queries working.

1. Don't use SUM, COUNT, etc in master query. Use it in query of queries. Also, don't use a group by in the Master query.

2. Use the table.column reference in the master query (for joins). Then, only use the column name in the query of queries. Use the SUM, Count, etc in the query of queries. Only select the columns you want to group by (the other columns were already called in the master query). Use the Group BY whatever column you selected in the select query.

Anyway, it is finally working! I imagine the loop statement would work too but I need to do some more reading.