• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Assistance with a tuf query

The following query return a list of forums and related info.
I need the query to return the number of threads in each forums and the total number of messages from all threads in that forum.
The table name for the threads is "sf_threads" and for the messages "sf_messages".
Thanks for the help. Hopefully I will learns from your answers.


select      
A.id,
A.name,
A.description,
A.readonly,
A.membersonly,  
A.active,
A.conferenceidfk,
B.name as conference,
B.sort_order as conference_order,
A.sort_order as forum_order,
A.groupidfk,
E.title as group_title,
A.msgcount as messagecount,
A.lastpost

from      
(forums A
inner join conferences B
on A.conferenceidfk = B.id)
left join usergroups E
on A.groupidfk=E.id

where      1=1

order by
B.sort_order,
A.sort_order
0
contiw
Asked:
contiw
1 Solution
 
Julian MatzJoint ChairpersonCommented:
Would something like this work? :

SELECT  forums.id, COUNT(threads.id) AS num_threads, COUNT(messages.id) AS num_messages
FROM forums
LEFT JOIN sf_threads AS threads
ON forums.id = threads.forum_id
LEFT JOIN sf_messages AS messages
ON forum.id = messages.forum_id;
0
 
mcmonapCommented:
Hi contiw,

I would do something like below to combine your query and additional requirements, I am guessing at the names of the columns in the message/thread tables are forumid but this is probably wrong and needs to be updated with the correct ones.
select      
	A.id
	, A.name
	, A.description
	, A.readonly
	, A.membersonly
	, A.active
	, A.conferenceidfk
	, B.name as conference
	, B.sort_order as conference_order
	, A.sort_order as forum_order
	, A.groupidfk
	, E.title as group_title
	, A.msgcount as messagecount
	, A.lastpost
	, T.threadCount
	, M.messageCount
from      
	forums A 
	join conferences B on A.conferenceidfk = B.id
	left join usergroups E on A.groupidfk = E.id
	left join (SELECT forumid, COUNT(*) as threadCount FROM sf_threads GROUP BY forumid) T on A.ForumID = T.ForumID
	left join (SELECT forumid, COUNT(*) as messageCount FROM sf_messages GROUP BY forumid) M on  A.ForumID = M.ForumID
order by
	B.sort_order
	, A.sort_order

Open in new window

0
 
Jinesh KamdarCommented:
Try this. Also, wat does the "msgcount" field in the forums table hold ?
SELECT f.id, COUNT(t.id), COUNT(m.id)
FROM forums f, sf_threads t, sf_messages m
WHERE f.id = t.forum_id
AND   t.id = m.thread_id
GROUP BY f.id;

Open in new window

0
 
contiwAuthor Commented:
Thank you macmonap. Excellent albeit extremely slow when using a 9600 messages. So I decided to add a column to keep a running count always updated for threads and messages. Your answer cannot be improved. Thanks again.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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