here is what u should dooooo. use the valuelist function in the second
queery. it basically is a comma delimited value of all the data from the
first query. see, uonly run 2 queries in all! :) teeheeehehee
<CFQUERY name="firstquery">
SELECT Maincat.id
FROM Maincat
WHERE Maincat.userid = #userid#
</CFQUERY>
<CFQUERY name"getSubcats">
SELECT count(*) as subcats
FROM tblsubcats
WHERE tblsubcast.maincatid IN ('#ValueList(firstquery.id)#')
</CFQUERY>
>From: "Bruce, Rodney" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: OT: Some Help W/SQL query
>Date: Tue, 12 Jun 2001 08:25:45 -0700
>
>I hope some one can help me with this problem, Hopefully a small one )
>
>
>I am doing a count with a query, but right now am looping through it to
>count each different category, Is there a way to do count on all
>categories
>running the query only once? this would save a lot of processing time for
>the page, as there can be anywhere from 1 to 5000 categories to loop
>through
>depending on what the user wants in the report. example code below:
>
>First query gets all the Main categories a user is associated with then
>loop
>through second query to get all the subcategories associated with each main
>category. Would like to only have to run the counting query once, is this
>possible?
>
>
><CFQUERY name="firstquery">
> SELECT Maincat.id
> FROM Maincat
> WHERE Maincat.userid = #userid#
></CFQUERY>
>
><CFLoop query="firstquery">
> <CFQUERY name"getSubcats">
> SELECT count(*) as subcats
> FROM tblsubcats
> WHERE tblsubcast.maincatid = #firstquery.id#
> </CFQUERY>
></CFLOOP>
>
>
>Thanks for any help
>Rodney
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
