Listing most commented news

Question

I have two tables in my database.

• Comments
  • CommentsID
  • MembersID
  • CommentsBy
  • CommentsDesc
  • CommentsActive
  • CommentsDateEntered
  • NewsID
    
    
    
• News
  • NewsID
  • MembersID
  • CategoriesID
  • ImagesID
  • NewsTitle
  • NewsShortDesc
  • NewsDesc
  • NewsActive

I need to take top 5 commented news (active comments and active news) and list their titles using one query. I hope I made sense. But I am really confused here. Any suggestion appreciated.

Answer 1

Is this what you want?

SELECT TOP(5) News.* 
FROM News 
WHERE News.NewsActive = 1 
ORDER BY 
    (SELECT COUNT(*) 
     FROM Comments 
     WHERE Comments.NewsId = News.NewsId AND Comments.CommentsActive = 1) DESC;

Response to your comment would be something like:

SELECT TOP(5) News.*, 
    (SELECT COUNT(*) 
     FROM Comments 
     WHERE Comments.NewsId = News.NewsId AND Comments.CommentsActive = 1) AS TotalComments
FROM News 
WHERE News.NewsActive = 1 
ORDER BY TotalComments DESC;