Running into a small problem with joins.
My category navigation is built through a query like this
<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.
<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
<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.
any help is appreciated.