help upgrading mailbox query - 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 2000 Forums

Transact-SQL (2000)

help upgrading mailbox query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-03-30 : 14:52:52

Hi,

I have a pretty straightforward query that just selects a users message. I need to upgrade it so that when looking at the message it brings back just the messageID of the previous and next messages (if they exist, maybe a 0 if they dont? )

I'm unsure of the best way to approach this one as speed is very very important. Is there a way to do it with JOINS ? Or should I add in
,( SELECT messageID WHERE UnSureOfThisPart as prevMessage )

Any tips are much appreciated..

thanks once again!!

CREATE PROCEDURE dbo.select_message
(
@messageID int,
@messageTo int
)
AS SET NOCOUNT ON

SELECT messageID, subject, messageTo, messageFrom, prevMessage, message, checked, nameOnline, tblmessage.date,

FROM tblMessage
JOIN UserDetails ON Message.messageFrom = userdetails.Userid

WHERE

messageID = @messageID AND
(MessageTo = @messageTo OR MessageFrom = @messageTo)

GO

jhermiz

3564 Posts

Posted - 2006-03-30 : 15:27:50

Not sure what you are asking, whats previous or next message?

Are you asking about

IF EXISTS(SELECT * FROM ...)
BEGIN
END
ELSE....
BEGIN
END

Or something else ?

Please provide sample data and more information

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-30 : 15:52:15

for ordering like that you would need a datetime column...

-- get current message date
declare @dt datetime
select @dt = YourDateColumn
from table1
where (messageId = @messageid) and ...

--previous
select top 1 ...
from table1
where (messageId = @messageid) and ...
and YourDateColumn < @dt
union all
-- current
select ...
from table1
where (messageId = @messageid) and ...
union all
-- next
select top 1 ...
from table1
where (messageId = @messageid) and ...
and YourDateColumn > @dt

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-03-30 : 16:19:59

Hey Guys,

I think what I want brought back is just 1 row, with 2 extra columns (prevMessageID, nextMessageID) rather than unioning 3 rows. Is there a way to do this incorporating your logic on determining the next and previous rows? I don't know how I would do it the way I'm approaching..

Something like this is maybe?

SELECT messageID, subject, messageTo, messageFrom, prevMessage, message, checked, nameOnline, tblmessage.date,

(SELECT messageID WHERE messageTo = @messageTo AND........) as prevMessageID ,(SELECT messageID WHERE messageTo = @messageTo AND.....) as nextMessageID ,

thanks very much for your help! much appreciated :)

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources