InstantASP Community Forums / Old Forums / InstantKB.NET 1.x / Query Article Count of all subarticles / Latest Posts

RE: Query Article Count of all subarticles

RobH — Thu, 29 Nov 2007 14:52:44 GMT

Well this took some figuring out and since I eventually got what I wanted I thought I would share.

This next SQL code gives a total count of articles which appear in both the nodes listed in the SQL. Great for counting duplicates. :D

[code]SELECT count(distinct questionid) AS count FROM instantkb_articlestreenodes

WHERE questionid IN

(

SELECT questionid FROM instantkb_articlestreenodes

WHERE nodeID IN ('2Q3U7V', '8W6V7H')

GROUP BY questionID

HAVING COUNT(*) > 1

)[/code]

RE: Query Article Count of all subarticles

RobH — Tue, 13 Nov 2007 10:37:34 GMT

Yes I know this is a 2 year old thread but it's as close as can be to what I need without actually giving me exactly what I need.

Let me explain (this isn't going to be easy)....

My SQL is at best err basic to n00b status so if you reply please be as plain as possible.

I have been tasked with coming up with a KB status page (web page) with various amounts of figures on it that the senior managers can look over and see what's what.

I've done most of what they want but the last one is killing me.

I want a category count per individual category as per this post BUT this is where it gets tricky. We have over 500 categories most END of the tree categories are named the same so we have

Product1 - with sub categories of faq, trouble shooting, training, user guides etc etc

product2 - with sub categories of faq, trouble shooting, training, user guides etc etc

select t.parentnodeid, t.nodename, count(*) 'Count' from instantkb_articlestreenodes AS a
join instantkb_tree as t
ON a.nodeid=t.nodeid
group by t.parentnodeid, a.nodeid, t.nodename
order by t.nodename

The above code gives me what I want but I kind figure out how to get the actual parentnodeid to show the actual nodename, in my example I have 0F6O0A - FAQ - 51 (parentnodeid - original nodeid - count). So in this example if it showed (parentnodeid as nodename - end nodename - count) that would be ace :)

And to top it all off I am hoping to make it so that when a new category gets added (yes there willl be more) I don't have to go back to the web page stats and alter the code.

Any kind sole help me recover some hair I pulled out ?? :)

RE: Query Article Count of all subarticles

Ryan Healey — Thu, 02 Jun 2005 06:30:29 GMT

Many thanks for your kind words Bill, your knowledge base is looking great :)

Please don't hesitate to contact us again if you have any further questions,

RE: Query Article Count of all subarticles

flyguy — Wed, 01 Jun 2005 20:57:39 GMT

Perfect! Our KB is now live (with modifications). Feel free to check it out at www.atalasoft.com/kb. A big improvement over the previous software we used. Thanks for your help and keep up the good work.

RE: Query Article Count of all subarticles

Carlos Muniz — Wed, 01 Jun 2005 14:58:28 GMT

[quote][b]flyguy (6/1/2005)[/b][hr]Thanks! But how would that query be modified to count articles within a specific category (and all sub categories)? That query seems to count all articles in the entire database.[/quote]

Dear flyguy,

Sorry, I misunderstood. Please check below to see if this procedure can solve your problem:

Create Procedure CountArticleInCategory(@strNodeID nvarchar(75))
AS

SELECT Count(DISTINCT (InstantKB_Articles.QuestionID)) AS QtyArticleInCategory FROM InstantKB_Articles INNER JOIN
InstantKB_ArticlesTreeNodes ON InstantKB_Articles.QuestionID = InstantKB_ArticlesTreeNodes.QuestionID INNER JOIN
InstantKB_TreeRoles ON InstantKB_ArticlesTreeNodes.NodeID = InstantKB_TreeRoles.NodeID INNER JOIN
InstantKB_Roles ON InstantKB_TreeRoles.RoleID = InstantKB_Roles.RoleID
WHERE InstantKB_TreeRoles.NodeID IN ( SELECT distinct(t4.NodeID) FROM InstantKB_Tree t1
INNER JOIN InstantKB_Tree t2 ON t1.Nodeid = t2.ParentNodeID or t2.Nodeid = @strNodeID
INNER JOIN InstantKB_Tree t3 ON t2.Nodeid = t3.ParentNodeID or t3.Nodeid = @strNodeID
INNER JOIN InstantKB_Tree t4 ON t3.Nodeid = t4.ParentNodeID or t4.Nodeid = @strNodeID
where t1.parentnodeid = @strNodeID or t1.Nodeid = @strNodeID )

return

RE: Query Article Count of all subarticles

flyguy — Wed, 01 Jun 2005 08:27:06 GMT

Thanks! But how would that query be modified to count articles within a specific category (and all sub categories)? That query seems to count all articles in the entire database.

RE: Query Article Count of all subarticles

Carlos Muniz — Wed, 01 Jun 2005 08:22:44 GMT

To Count all articles:

select count(DISTINCT(InstantKB_Articles.QuestionID))As QuantityofArticles FROM InstantKB_Articles INNER JOIN
InstantKB_ArticlesTreeNodes ON InstantKB_Articles.QuestionID = InstantKB_ArticlesTreeNodes.QuestionID INNER JOIN
InstantKB_TreeRoles ON InstantKB_ArticlesTreeNodes.NodeID = InstantKB_TreeRoles.NodeID INNER JOIN
InstantKB_Roles ON InstantKB_TreeRoles.RoleID = InstantKB_Roles.RoleID

To Count only public articles:

You can change the where condition as you please. eg.: if you would like to see only public and customers articles you need to check via select * from InstantKB_Roles where the RoleID is the customer role id and then add OR statements like below:

where InstantKB_TreeRoles.RoleID = 1 or InstantKB_TreeRoles.RoleID = 3

Query Article Count of all subarticles

flyguy — Tue, 31 May 2005 13:51:53 GMT

We have been customizing InstantKB to fit our needs, and the one area where we could use a little help is writing a query that will return the article count of a category and all subcategories. Currently we have written a recursive routine which loops through all articles of the parent and child categories for a count. However for articles that appear in multiple categories are counted twice. I'm not sure the current database structure is really setup for this type of heirarchy, but is there a SQL Query you can supply us to count all articles including those in subcategories?

Thanks,
-Bill