Problem with display RECORDCOUNT and JOINS

Running into a small problem with joins.

My category navigation is built through a query like this

Code:

<cfquery name="categories" datasource="#dsn#"> SELECT * FROM tblCategory ORDER BY categoryname ASC </cfquery>



Now, I want to display next to the category the number of stories that particular category has in it.

Hence, this.

Code:

<cfoutput query="categories"> <li><a href="type-choose.cfm?categoryID=#categories.categoryID#" title="#categories.categoryname#">#categories.categoryname# #RecordCount#</a></li> </cfoutput>



I tried doing this

Code:

<cfquery name="categories" datasource="#dsn#"> SELECT c.categoryname, c.categoryID, s.storyID FROM tblCategory as c LEFT OUTER JOIN tblstory as s ON c.categoryID = s.categoryID ORDER BY categoryname ASC </cfquery>



but that obviously didn't work. I tried LEFT INNER JOIN and INNER JOIN, and both gave me not the numbers I am looking for.

tblstory has a field called categoryID. and then tblcategory has 2 fields, categoryID and categoryname.

I just want to list down my page all the categories from that table. And then , next to each name, display the number of stories that are IN that category.

Whats wrong with my query.

Thanks

any help is appreciated.

Thanks
jag5311Asked:
Who is Participating?
 
moduloCommented:
PAQed with points refunded (50)

modulo
Community Support Moderator
0
 
sigmaconCommented:
Try this query to get categories and and the story count.  Instead of trying to print #RecordCount# you need to print #numberOfStories# when <cfoutput>ting the query

SELECT
    c.categoryname,
    c.categoryID,
    (SELECT count(s.storyID) FROM tblstory as s WHERE c.categoryID = s.categoryID) as numberOfStories
FROM tblCategory as c
ORDER BY categoryname ASC
0
 
jag5311Author Commented:
I ended up using this

select c.categoryID
     , categoryname
     , count(s.storyID) as stories
  from tblcategory  as c
left outer
  join tblstory as s
    on c.categoryID
     = s.categoryID
group
    by c.categoryID
     , categoryname    
order
    by categoryname asc

But I am going to go ahead and give you the 50 points.

Thanks for the response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.