Complex query help - 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

Author

Topic

gxs8938
Starting Member

23 Posts

Posted - 2010-01-21 : 01:27:39

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 set up like below:
select distinct message.threadid as threadid,message.subject ,datecre,Replymessage as lastmessage,Author as sender,
datereplied as date from message
left outer join ReplyMessage on authorid = 2 and message.threadid=replymessage.threadid

But this gives me all the messages and its reply messages. I need all the messages with the most recent reply message.

Please let me know how to change my query.

Query results:
247,fgdfkjg fdskglj dfglkdfj gdfklsg; fdgl;kfds gfl;dk ,2010-01-18 18:40:11.970,,mboyce,2010-01-20 18:37:25.663
247,fgdfkjg fdskglj dfglkdfj gdfklsg; fdgl;kfds gfl;dk ,2010-01-18 18:40:11.970,,mboyce,2010-01-20 19:18:19.823
247,fgdfkjg fdskglj dfglkdfj gdfklsg; fdgl;kfds gfl;dk ,2010-01-18 18:40:11.970,,mboyce,2010-01-21 00:10:32.843

result needed: only one row with 247 id.

Please help

Regards,
Guru

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 04:10:43

Please use the below code....

select message.threadid as threadid,
max(datereplied) as date into #temp from message
left outer join ReplyMessage on authorid = 2 and message.threadid=replymessage.threadid
group by message.threadid

select #temp.threadid as threadid,
message.subject ,datecre,Replymessage as lastmessage,Author as sender,
#temp.date from message
left outer join ReplyMessage on authorid = 2 and message.threadid=replymessage.threadid
inner join #temp
on #temp.threadid=ReplyMessage.threadid and #temp.date=ReplyMessage.datereplied
drop table #temp

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 05:30:12

Why the use of a temp table? using a temp table in this way will cause query plan recompiles each time the the query is run. Isn't this all you need as you are on 2005...


SELECT
	m.[threadId]
	, m.[subject]
	, m.[datecre]
	, rm.[replyMessage]
	, rm.[author]
	, rm.[datereplied]
FROM
	message m

	LEFT JOIN (
		SELECT
			[threadID] AS [threadId]
			, [replyMessage] AS [replyMessage]
			, [autor] AS [author]
			, [dateReplied] AS [dateReplied]
			, ROW_NUMBER() OVER (PARTITION BY [threadID] ORDER BY [dateReplied] DESC) AS [pos]
		FROM
			replyMessage
		WHERE
			[authorID] = 2
		)
		rm ON rm.[threadId] = m.[threadid] AND rm.[pos] = 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 05:35:05

you could also use OUTER APPLY I guess though I find the ROW_NUMBER methods easier to read. OUTER APPLY may give you better index usage though.

 
SELECT
	m.[threadId]
	, m.[subject]
	, m.[datecre]
	, rm.[replyMessage]
	, rm.[author]
	, rm.[datereplied]
FROM
	message m

	OUTER APPLY (
		SELECT TOP 1
			rmes.[replyMessage] AS [replyMessage]
			, rmes.[autor] AS [author]
			, rmes.[dateReplied] AS [dateReplied]
		FROM
			replyMessage rmes
		WHERE
			rmes.[threadID] = m.[threadID]
			AND rmes.[authorID] = 2
		ORDER BY
			rmes.[dateReplied] DESC
		)
		rm

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gxs8938
Starting Member

23 Posts

Posted - 2010-01-21 : 13:56:15

Thanks guys , It worked like magic :)

Regards,
Guru

gxs8938
Starting Member

23 Posts

Posted - 2010-01-22 : 01:01:52

quote:
Originally posted by Transact Charlie

you could also use OUTER APPLY I guess though I find the ROW_NUMBER methods easier to read. OUTER APPLY may give you better index usage though.

SELECT
m.[threadId]
, m.[subject]
, m.[datecre]
, rm.[replyMessage]
, rm.[author]
, rm.[datereplied]
FROM
message m

OUTER APPLY (
SELECT TOP 1
rmes.[replyMessage] AS [replyMessage]
, rmes.[autor] AS [author]
, rmes.[dateReplied] AS [dateReplied]
FROM
replyMessage rmes
WHERE
rmes.[threadID] = m.[threadID]
AND rmes.[authorID] = 2
ORDER BY
rmes.[dateReplied] DESC
)
rm

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I have a problem here authorid is from message. so its giving all the messages . its not for that particulat user.

How to get around this??

Regards,
Guru

gxs8938
Starting Member

23 Posts

Posted - 2010-01-22 : 02:14:05

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 04:09:25

So in the second case you don't actually want the rows to appear?

If so then just change the Line


OUTER APPLY ....


CROSS APPLY ....

It's analogous to a LEFT JOIN compared to an INNER JOIN

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gxs8938
Starting Member

23 Posts

Posted - 2010-01-22 : 15:52:17

I got it solved thanks charlie.

Regards,
Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources