Thanks for all the good ideas, but I need the subcatcount for each main cat
to stay separate, sorry if I didn't give enough details: all suggestions I
would loose the individual counts
My out put looks something like this:
User - User Name
Main Category Number of Subcategories
Main Cat 1 8
Main Cat 2 9
Main Cat 3 500
I would need to be able to keep each sub category total.
This is printed within the loop now like so:
<CFQUERY name="firstquery">
SELECT Maincat.id, Maincat.name
FROM Maincat
WHERE Maincat.userid = #userid#
</CFQUERY>
User Name - #username#
Main Category Number of Sub Categories
<CFLoop query="firstquery">
<CFQUERY name"getSubcats">
SELECT count(*) as subcats
FROM tblsubcats
WHERE tblsubcast.maincatid = #firstquery.id#
</CFQUERY>
#firstquery.name# #getsubcats.subcats#
</CFLOOP>
Again sorry if I confused you with not enough details, or if I am missing
something in one of your answers, but when I run them I get a final total
of all subcats for all maincats. I do like a couple of the suggestions, I
can use in other areas of my site :o).
Thanks for all the help
Rodney
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 12, 2001 10:38 AM
To: CF-Talk
Subject: RE: Some Help W/SQL query
try this...
(this is an INNER JOIN version, you can probably adapt it to an outer join
yourself if necessary)
SELECT mc.id, count(sc.subCatID) AS subCatCount
FROM maincat mc, tblSubCats sc
WHERE mc.id = sc.mainCatID
GROUP BY mc.id, sc.subCatID
Bryan Love ACP
Internet Application Developer
[EMAIL PROTECTED]
-----Original Message-----
From: Gary Longford [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 12, 2001 9:21 AM
To: CF-Talk
Subject: RE: Some Help W/SQL query
How about?
<CFQUERY name="qryOneQuery">
SELECT Maincat.id,
(SELECT count(*) as subcats FROM tblsubcats WHERE
tblsubcast.maincatid = Maincat.id) AS CategoryCount
FROM Maincat
WHERE Maincat.userid = #userid#
</CFQUERY>
This is assuming that some categories have 0 records
Yours,
Gary Longford
-----Original Message-----
From: Bruce, Rodney [mailto:[EMAIL PROTECTED]]
Sent: 12 June 2001 16:26
To: CF-Talk
Subject: OT: Some Help W/SQL query
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
