Help with Join operation in SQL

Question

Forums

ID
Name
Description

Questions

ID
UserID
ForumID
Title
QuestionText
CreateDateTime

Replies

ID
UserID
QuestionID
ReplyText
CreateDateTime

users

UserID
UserName

I want to retrieve name of each forum, number of questions of each forum, number of replies of each forum, last post's UserName and date for each forum.

Answer 1

declare @Forums table (ID int identity primary key, Name varchar(30), Description varchar(600))
insert into @Forums values ('SQL Server - T-SQL','All T-SQL related questions'),('Getting Started','All novice questions about SQL Server'),('Database Engine','Questions about internal working of SQL Server')

declare @Questions table 
(ID int identity primary key, ForumID int, UserID int, Title varchar(100), QuestionText varchar(max), CreateDateTime datetime)
insert into @Questions values (1,1, 'Need Help with a query','Hi please help',getdate())
insert into @Questions values (1,2,'Complex query','Hi please help',getdate())
insert into @Questions values (1,3,'Running total problem','Hi please help',getdate())
insert into @Questions values (2,3,'Discussion','I found this interesting problem when I was writing my blog',getdate())

declare @Replies table 
(ID int identity primary key, UserID int, QuestionID int, ReplyText varchar(max), CreateDateTime datetime)
insert into @Replies values (2,1, 'Try the following solution',getdate())
insert into @Replies values (1,2,'I think this problem can be solved',getdate())
insert into @Replies values (1,3,'See the following thread ...',getdate())

declare @Users table (UserID int identity primary key, UserName varchar(100))
insert into @Users values('Adam Haines'), ('Plamen Ratchev'), ('Brad Schulz')

;with NumQuestions as 
(select ForumID, count(ID) as NumberOfQuestions 
from @Questions group by ForumID), 
LastPost as (select ForumID, UserID, 
CreateDateTime, NumberOfReplies from (select Q.ForumID, 
COALESCE(R.UserID, Q.UserID) as UserID, 
coalesce(R.CreateDateTime, Q.CreateDateTime) as CreateDateTime,
count(R.ID) over (partition by Q.ForumID) as NumberOfReplies, 
row_number() over (partition by Q.ForumID 
order by coalesce(R.CreateDateTime,Q.CreateDateTime) DESC) as RowNum 
from @Questions Q 
LEFT join @Replies R on Q.ID = R.QuestionID) X where RowNum = 1)

select F.Name as ForumName, F.ID as ForumID,  ISNULL(N.NumberOfQuestions,0) as [Number of Questions], 
ISNULL(L.NumberOfReplies,0) as [Number of Replies], 
U.UserID, U.UserName as [Last User], L.CreateDateTime as [Last Post Date/Time] from @Forums F 
LEFT JOIN NumQuestions N on F.ID = N.ForumID
LEFT JOIN LastPost L on F.ID = L.ForumID
LEFT JOIN @Users U on L.UserID = U.UserID