Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
brettrg
Starting Member
7 Posts |
 Posted - 2006-02-13 : 15:24:07
|
| Hello,I'm developing a very simple discussion forum for our intranet. I'd like to show similar info to that which sqlteam.com shows when looking at a list of forums (Forum Name, #Topics, #Posts, Last Post).The structure is divided into a 3 table hierarchy: Forum_Categories, Forum_Topics, Forum_Posts.I'm having trouble with my query that would show a list of the categories, the number of topics and posts in each category, the most recent active topic as well as the post date and post author for that topic. Here is what i've got:-------------------------------------------------SELECT Forum_Categories.CatID, Forum_Categories.CatName, Forum_Categories.CatDesc, (SELECT COUNT(TopID) FROM Forum_Topics WHERE CatID = Forum_Categories.CatID) AS NumTopics, COUNT(Forum_Posts.PostID) AS NumPosts, MAX(Forum_Posts.PostDate) AS LastPostDateFROM Forum_PostsLEFT OUTER JOIN Forum_Topics ON Forum_Posts.TopID = Forum_Topics.TopIDRIGHT OUTER JOIN Forum_Categories ON Forum_Topics.CatID = Forum_Categories.CatIDGROUP BY Forum_Categories.CatID, Forum_Categories.CatName, Forum_Categories.CatDescORDER BY Forum_Categories.CatID------------------------------------------------------------------You'll notice i'm not doing anything yet for last topic heading and last post author. When i add these fields in the query gets messed up.Any help would be appreciated.Thanks! |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-02-15 : 06:07:11
|
| Without commenting on your immediate technical problem, would it not be be simpler/easier to go back to basics and look to 'reuse cheap/free' software?...like that used by this forum itself....ie Snitz.com...and just cull/not implement the bits you don't need. |
 |
|
|
|
|
|
|
|
Discussion Forum Query