We help IT Professionals succeed at work.

Help With Select Complex Query

clickclickbang
on
241 Views
Last Modified: 2012-06-27
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.
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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

Author

Commented:
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?

Author

Commented:
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.
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

Author

Commented:
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.
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.

Author

Commented:
Thank you guys for the help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.