asked on

Help With Select Complex Query

Experts,

I am building a forum and need to create a query to select a post, the total threads under the post, the total replies under each thread, and the total views for all replies and threads.

My table structure is:

ForumPostId
ParentForumPostId
Subject
Post
UserId
UpdatedUserId

So my target columns would be:

Subject,
Post,
TotalThreads, (count of posts with the current post as the ParentForumPostId)
TotalReplies, (count of posts with the ParentForumPostId as the Id of the post above)
Views,
UserFirstName,
UserLastName

Here is what I have so far to select all topics:

Select [fto].*,
(Select count(ForumPostId) from [ForumPosts] where ([ParentForumPostId] = [fto].[ForumPostId]) ) as [TotalThreads],
(Select count(ForumPostId) from [ForumPosts] where ([ParentForumPostId] = [ForumPostId]) ) as [TotalReplies],
(Select sum([Views]) from [ForumPosts] where ([ForumPostId] = [fto].[ForumPostId]) ) as [TotalViews],
(Select [Users].[FirstName] from [Users] where ([UserId] = [fto].[UpdatedUserId]) ) as [LastUpdateUserFirstName],
(Select [Users].[LastName] from [Users] where ([UserId] = [fto].[UpdatedUserId]) ) as [LastUpdateUserLastName]
from [ForumPosts] [fto] where [fto].[ParentForumPostId] is null
order by [DisplayOrder] asc;

Sample Data Structure:

ForumPostId ParentForumPostId Subject
1 null This would be a topic
2 1 This would be a thread
3 2 This would be a reply

The only thing I can't get is how to get the replies. I got a little confused writing the question, so if this doesn't make sense let me know and I'll try to explain.

ASKER CERTIFIED SOLUTION

kprestage

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

JohnDesautels

membership

This solution is only available to members.

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

Start Free Trial

JohnDesautels

I forgot to alias the second table as C. Try this instead.

SELECT P.Subject,
P.Post,
SUM(CASE WHEN C.ParentForumPostId IS NULL THEN 1 ELSE O END) TotalThreads,
SUM(CASE WHEN C.ParentForumPostId IS NOT NULL THEN 1 ELSE O END) TotalReplies,
P.Views,
U.UserFirstName,
U.UserLastName

FROM ForumPosts P /* Parent */
LEFT OUTER JOIN ForumPosts C /*Child */
ON P.ForumPostId = C.ParentForumPostId
INNER JOIN Users U
ON P.UpdatedUserId = U.UserId

John

back to top

clickclickbang

ASKER

John, thank you for your post. I modified the table names a bit to match my schema, however, I'm getting a few errors:

SELECT P.Subject,
P.Post,
SUM(CASE WHEN C.ParentForumPostId IS NULL THEN 1 ELSE 0 END) TotalThreads,
SUM(CASE WHEN C.ParentForumPostId IS NOT NULL THEN 1 ELSE 0 END) TotalReplies,
P.Views,
U.FirstName,
U.LastName

FROM ForumPosts P /* Parent */
LEFT OUTER JOIN ForumPosts C /*Child */
ON P.ForumPostId = C.ParentForumPostId
INNER JOIN Users U
ON P.UpdatedByUserId = U.UserId

Server: Msg 8118, Level 16, State 1, Line 1
Column 'P.Subject' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'P.Post' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'P.Views' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'U.FirstName' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'U.LastName' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Any thoughts?

clickclickbang

ASKER

Also, where you had:
SUM(CASE WHEN C.ParentForumPostId IS NULL THEN 1 ELSE O END) TotalThreads,
SUM(CASE WHEN C.ParentForumPostId IS NOT NULL THEN 1 ELSE O END) TotalReplies,

I changed the "O" as in owl, to zeros.

kprestage

SELECT P.Subject,
P.Post,
SUM(CASE WHEN C.ParentForumPostId IS NULL THEN 1 ELSE 0 END) TotalThreads,
SUM(CASE WHEN C.ParentForumPostId IS NOT NULL THEN 1 ELSE 0 END) TotalReplies,
P.Views,
U.FirstName,
U.LastName

FROM ForumPosts P /* Parent */
LEFT OUTER JOIN ForumPosts C /*Child */
ON P.ForumPostId = C.ParentForumPostId
INNER JOIN Users U
ON P.UpdatedByUserId = U.UserId
group by P.Subject, p.Post, p.Views, U.FirstName, U.LastName

clickclickbang

ASKER

KP - Thanks for the quick fix. You guys are both great!

I do have one other issue and I'd like to get your input. Right now I have the post column as a "text" data type. Now with that, I'm getting the error:

Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I've always been up in the air about when to 'text' and when not to 'text'. Essentially this will be a forum and accept posts that can be quite long. What would you guys suggest in regards to the appropriate data type for my post column? Also, if you suggest 'text', how then could I modify this query to work with that data type?

Thanks again for your help, I've already solved two other issues I was facing using concepts from your examples.

kprestage

What version of SQL Server are you running? If you are running 2000, then id a post is going to be more than 8000 characters your only choice is text. If you are running 2005, you have the option of using varchar(Max) which will alllow 2^31 bytes of data, or 2,147,483,648 characters. Text is great for storing large amounts of characters and being able to search on them, but you do have to jump through some hoops to get it working. In your case, you would probably need to remove POst from the query all together and retireve the text in a seperate query.

clickclickbang

ASKER

Thank you guys for the help.