Solved

Problem with display RECORDCOUNT and JOINS

Posted on 2004-10-23
165 Views
Last Modified: 2013-12-24
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
Question by:jag5311
    3 Comments
     
    LVL 8

    Expert Comment

    by:sigmacon
    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
     

    Author Comment

    by:jag5311
    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
     

    Accepted Solution

    by:
    PAQed with points refunded (50)

    modulo
    Community Support Moderator
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now