multiple queries and cfgrid

[rmorgan]

Hi,
I have a soccer(futbol, for some of you folks) standings page. I have it all
working perfectly within a standard html page just fine. I want to change it
over to a cfgrid. The problem is, I have multiple queries, and it seems as if
cfgrid can only handle one query. I have 2 main queries and about 13 different
subqueries that I would like to combine into one so that I can use cfgrid. I
wont bore you with every single query but if I can be pointed in the right
direction, I am sure I can figure the rest out.

First main query:
<cfquery name="getteams" datasource="#ds#">
SELECT teamname
FROM teams
WHERE age_id = '#ageid#'
group BY team_id
</cfquery>

Second main Query:
<cfquery datasource="#ds#" name="getscores">
SELECT home, homescore, visitor, visitorscore, homeforfeit, visitorforfeit
FROM schedule
WHERE age_id = '#ageid#'
AND (home = '#getteams.teamname#'
OR visitor = '#getteams.teamname#')
</cfquery>

First QofQ:
<cfquery name="gethwins" dbtype="query">
SELECT home
FROM getscores
WHERE homescore > visitorscore
AND home = '#getteams.teamname#'
</cfquery>

Second QofQ:
<cfquery name="gethloss" dbtype="query">
SELECT homescore
FROM getscores
WHERE homescore < visitorscore
AND home = '#getteams.teamname#'
</cfquery>

Third QofQ:
<cfquery name="gethgf" dbtype="query">
select sum(homescore) as hgf
from getscores
where home = '#getteams.teamname#'
and homescore is not null
</cfquery>

Like I said there are several other QofQ's, but this is the jist of it. Can
someone help me out on what I need to do combine these into one query so that I
can use cfgrid? TIA.

[mxstu]

You might look into either sql subqueries or using some type of CASE statements
(ie. sql 'case' operator or your database's equivalent). However, a lot
depends on the different levels of information you are trying to display in
your final results and how many tables you are querying. What tables are you
querying (overall)? What information are you retreiving/displaying?