you might have missed my earlier response; it would keep the count
seperated... (see below)
Bryan Love ACP
Internet Application Developer
[EMAIL PROTECTED]
-----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
