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
contiwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian M.Web DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.