Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Problem with display RECORDCOUNT and JOINS

Posted on 2004-10-23
Medium Priority
Last Modified: 2013-12-24
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.

Hence, this.


<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.

Question by:jag5311
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12391721
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 count(s.storyID) FROM tblstory as s WHERE c.categoryID = s.categoryID) as numberOfStories
FROM tblCategory as c
ORDER BY categoryname ASC

Author Comment

ID: 12396932
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
    by c.categoryID
     , categoryname    
    by categoryname asc

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

Thanks for the response.

Accepted Solution

modulo earned 0 total points
ID: 12747366
PAQed with points refunded (50)

Community Support Moderator

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question