MySQL 5.0 & Coldfusion - Using COUNT(*) and cfoutput?

[j0ned]

This is what I'm trying to achieve...

I want to display how many comments have been made on my articles. Database is setup like so:

Two tables...

Table1 = tblmainindex holds articles/posts, date/time of post, article title, and of course contains the unique primary identifier labled: "rowid"

Table2 = tblcomments holds comments made on articles. when someone submits a comment, the associated "rowid" from tblmainindex is stored in a column labled "articleid" - so each comment is associated with each article's tblmainindex.rowid

Hope that made sense, if not, please ask for clarity... NOW, I've got the query built, and it runs fine in the Query Browser outputting the results I want. But when I try to cfoutput, it's only displaying the count for the first row, NOT for the associated article.

I'd like the COUNT to display for each seperate article.. If this makes no sense, please inquire!

Thanks for your help... And here's the query:


<cfquery name="qry_comment_count" datasource="#datasource#">
SELECT count(*) as articlecount, a.rowid
FROM tblmainindex a, tblcomments b
WHERE a.rowid = b.articleid
GROUP BY a.rowid
</cfquery>


I attached the results of the query in hopes this will make thing clearer.

Thanks again!

[ccoonen]

Your going to want to do the Count in a Sub-Select and join on the RowID so you get a count of each Row

As in:
Select id, counter.thecount
from table1 inner join
(select count(id) as thecount from table2) counter on table1.id = table2.id
order by counter.thecount desc

[datropics]

pretty slick ccoonen!!! I second that motion!