<cfquery name="countquery1">
select count(case_id) As ThisCount1 from table1
left outer join tablemain on tablemain.sectionname =
table1.x
group by sectionname
order by sectionname
</cfquery>
<cfquery name="countquery2">
select count(case_id) As ThisCount2 from table2
left outer join tablemain on tablemain.sectionname =
table1.x
group by sectionname
order by sectionname
</cfquery>
<cfquery name="countquery3">
select count(case_id) As ThisCount3 from table2
left outer join tablemain on tablemain.sectionname =
table1.x
group by sectionname
order by sectionname
</cfquery>
<cfoutput query="countquery1">
#countquery1.thiscount1[currentrow]#
#countquery2.thiscount2[currentrow]#
#countquery3.thiscount3[currentrow]#
</cfoutput>
--- [EMAIL PROTECTED] wrote:
> Hi all,
>
> I've got a main <cfloop> looping through a set of
> records - say 15 at a time - and within that loop
> I've got 3 SQL queries performing a count on how
> many associated records there are for that
> particular record in the main loop. Kind of like a
> forum index page to give how many threads, posts etc
> there are for each forum.
>
> i.e. (rough code to give you an idea of what I
> mean):
>
> <cfquery name="mainquery">
> select * from tablemain
> </cfquery>
>
> <cfloop query="mainquery" startrow="1" endrow="15">
>
> <td>#SectionName#</td>
>
> <cfquery name="countquery1">
> select count(case_id) As ThisCount1 from table1
> where x = '#mainquery.SectionName#'
> </cfquery>
> <td>#countquery1.ThisCount1#</td>
>
> <cfquery name="countquery2">
> select count(case_id) As ThisCount2 from table2
> where x = '#mainquery.SectionName#'
> </cfquery>
> <td>#countquery2.ThisCount2#</td>
>
> <cfquery name="countquery3">
> select count(case_id) As ThisCount3 from table3
> where x = '#mainquery.SectionName#'
> </cfquery>
> <td>#countquery3.ThisCount3#</td>
>
> </cfloop>
>
> My question is, is there a more efficient method for
> doing this kind of thing? As the tables I am
> accessing have 1,000s of rows - possibly millions -
> the page takes a significant amount of time finish
> processing. I could remove the record counts
> altogether, but it's preferable for it to remain.
>
> Cheers,
> Rob
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
