Query for a Forum [message #2578] |
Mon, 29 July 2002 05:28 |
Lisa
Messages: 31 Registered: September 2000
|
Member |
|
|
I am trying to write a query for a discussion forum.
I have 3 tables: FORUMS, TOPICS, and POSTS. I want to bring back all the info from the FORUMS table, along with a count of all the topics for that forum, and another count of all the posts for that forum (these do not need to be categorized by topic).
So my resulting table would look something like this:
FORUMS.ID, FORUMS.NAME, FORUMS.DESCRIPTION, TOPICSCOUNT, POSTSCOUNT
The tables can be linked as follows:
FORUMS.ID = TOPICS.FORUM_ID
TOPICS.ID = POSTS.TOPIC_ID
Does anyone know a way to write this as a single SQL statement? I think I know a way to do it using PL/SQL, but would to use just one query to do this if possible.
Thanks!
|
|
|
Re: Query for a Forum [message #2584 is a reply to message #2578] |
Mon, 29 July 2002 09:19 |
Silpa
Messages: 23 Registered: July 2002
|
Junior Member |
|
|
select
FORUMS.ID,FORUMS.NAME,FORUMS.DESCRIPTION,
COUNT(TOPICS.PRIMARYKEY) TOPICSCOUNT,
COUNT(POSTS.PRIMARYKEY) POSTSCOUNT
FROM FORUMS,TOPICS,POSTS
WHERE
FORUMS.ID = TOPICS.FORUM_ID
TOPICS.ID = POSTS.TOPIC_ID
GROUP BY FORUMS.ID,FORUMS.NAME,FORUMS.DESCRIPTION;
|
|
|