[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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
0
jag5311
Asked:
jag5311
1 Solution
 
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
 
moduloCommented:
PAQed with points refunded (50)

modulo
Community Support Moderator
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now