complex query help extended - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex query help extended

Author

Topic

gxs8938
Starting Member

23 Posts

Posted - 2010-01-22 : 01:21:59

Table1 structure
[ID] [int] IDENTITY(1,1) NOT NULL,
[AuthorID] [int] NOT NULL,
[ThreadID] [int] NOT NULL,
[ResponseTo] [int] NULL,
[Subject] [nvarchar](128) NOT NULL CONSTRAINT [DF_Message_Subject] DEFAULT (N''),
[Body] [nvarchar](max) NOT NULL CONSTRAINT [DF_Message_Body] DEFAULT (N''),
[LastEditDate] [datetime] NOT NULL CONSTRAINT [DF_Message_LastEditDate] DEFAULT (getdate()),
[DateCre] [datetime] NOT NULL CONSTRAINT [DF_Message_DateCre] DEFAULT (getdate()),
[DateMod] [datetime] NOT NULL CONSTRAINT [DF_Message_DateMod] DEFAULT (getdate()),
[Attachments] [image] NULL,
[AttachmentType] [nvarchar](50) NULL,
[AttachmentTitle] [nvarchar](max) NULL,
[AttachmentSize] [int] NULL,
[Category] [nvarchar](max) NULL,
[temp] [nchar](10) NULL,
[AttachmentPath] [nvarchar](max) NULL,

Table2 structure:
[ThreadID] [int] NOT NULL,
[ReplyMessage] [nvarchar](max) NULL,
[Author] [nvarchar](max) NULL,
[DateReplied] [datetime] NULL,
[Attachments] [image] NULL,
[AttachmentType] [nvarchar](max) NULL,
[Title] [nvarchar](max) NULL,
[AttachmentSize] [int] NULL

I need to get a new posted message list along with the last reply message for that(most recent reply only)

I have a query(helped by charlie in this forum) set up like below:

WITH tempLog AS (SELECT ROW_NUMBER() OVER (ORDER BY m.threadid DESC)AS Row,
m.[threadId] as threadid
, m.[subject] as subject
, m.[datecre] as datecre
, rm.[replyMessage] as lastmessage
, rm.[author] as sender
, rm.[datereplied] as date
FROM
message m

OUTER APPLY (
SELECT TOP 1
rmes.[replyMessage] AS [replyMessage]
, rmes.[author] AS [author]
, rmes.[dateReplied] AS [dateReplied]
FROM
replyMessage rmes
WHERE
rmes.[threadID] = m.[threadID]
AND m.authorID = 2
ORDER BY
rmes.[dateReplied] Desc
)
rm)SELECT threadid,subject,
datecre,lastmessage,sender,date
FROM tempLog
WHERE Row between ((1 - 1) * 10 + 1) and (1*10)

It works fine and i get result as expected , but Its listed by user.
m.authorID =2 doesn't have any effect. It simply list all the messages.

Please let me know where to put that m.authorid= 2??

Regards,
Guru

gxs8938
Starting Member

23 Posts

Posted - 2010-01-22 : 01:24:05

To elaborate it does not list messages by userid. Its simply list all the messages.

gxs8938
Starting Member

23 Posts

Posted - 2010-01-22 : 02:16:11

authorID=2 works only within the subquery. the outer select statement always returns values

for example for authorid = 2 the output is
247,fgdfkjg fdskglj dfglkdfj gdfklsg; fdgl;kfds gfl;dk ,2010-01-18 18:40:11.970,,mboyce,2010-01-21 15:09:04.760
246,test%20post,2010-01-18 11:45:03.590,Blah,NULL,2010-01-18 17:39:09.503
245,test,2010-01-16 18:30:07.830,Djxjdjsdhdb,NULL,2010-01-18 16:51:13.237

which is correct

But for authorid =67 who does not own any conversation it gives.
247,fgdfkjg fdskglj dfglkdfj gdfklsg; fdgl;kfds gfl;dk ,2010-01-18 18:40:11.970,NULL,NULL,NULL
246,test%20post,2010-01-18 11:45:03.590,NULL,NULL,NULL
245,test,2010-01-16 18:30:07.830,NULL,NULL,NULL

seems it only checking for inner subquery , how to add the where statement for first select??

gxs8938
Starting Member

23 Posts

Posted - 2010-01-22 : 02:27:04

I solved it thanks all.

Regards,
Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources