top button
Flag Notify
Site Registration

Help with Join operation in SQL

+1 vote
291 views

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.

posted Jun 16, 2015 by Shivaranjini

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer
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
answer Jun 17, 2015 by Manikandan J
Similar Questions
+1 vote

LookupTable:

userid, mobileid, startedate, enddate , owner
 1 , 1 , 12-12-2000, 01-01-2001, asd 
2 , 2 , 12-12-2000, 01-01-2001, dgs 
3 , 3 , 02-01-2001, 01-01-2002, sdg
 4 , 4 , 12-12-2000, 01-01-2001, sdg

UserInfoTable:

userid, firstname, lastname, address 
1 , tom , do , test 
2 , sam , smith , asds 
3 , john , saw , asdasda
 4 , peter , winston , near by

Mobile:

Mobileid, Name , number, imeinumber 

1 , apple , 123 , 1111111
 2 , nokia , 456 , 2222222
 3 , vodafone , 789 ,` 3333333

CallLogs:

id , Mobileid, callednumbers (string), date , totalduration 
1 , 1 , 123,123,321 , 13-12-2000 , 30 2 , 1 , 123,123,321 , 14-12-2000 , 30 3 , 2 , 123,123,321 , 13-12-2000 , 30 4 , 2 , 123,123,321 , 14-12-2000 , 30 5 , 3 , 123,123,321 , 13-12-2000 , 30 6 , 3 , 123,123,321 , 14-12-2000 , 30 7 , 1 , 123,123,321 , 13-01-2002 , 30 8 , 1 , 123,123,321 , 14-01-2002 , 30

I want a query which will return me the following:
firstname, lastname, mobile.name as mobilename, callednumbers (as concatinated strings from different rows in CallLogs table) and need it for year 2000 example:

firstname, lastname, mobilename, callednumbers 
tom , do , apple , 123,123,321, 123,123,321
 sam , smith , nokia , 123,123,321, 123,123,321
 peter , winston , apple , 123,123,321, 123,123,321

any help will be highly appreciated...

...