asked on

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

Julian Matz

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;

ASKER CERTIFIED SOLUTION

mcmonap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jinesh Kamdar

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

contiw

ASKER

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.