sql 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

General SQL Server Forums

New to SQL Server Programming

sql query

Author

Topic

ameya_amu
Starting Member

25 Posts

Posted - 2007-03-27 : 11:54:46

i am preparing module like email which contain subject, content, enclosure, etc and this mail is forwarded to many persons. if person A create a mail and forward it to B and then B makes some remark and forwad it to C.

i have created 2 table 1)MainMail 2)forwardedMail

MainMail Content
Mid
From
To
Subject
Content
Encl

Forwarded Content
Fid
Mid
From
To
Remark

forwarded table is displayed as this
Fid Mid From To Remark
1 1 A B Tsds
2 1 B C fdgdf
3 1 C D jgjg
4 1 D B jhjkh
5 1 B A hjghj

i want following output in single query
1) Subject(from MainMail):- test
Content(from MainMail):- te
From To Remark
A B Tsds
B C fdgdf
C D jgjg
D B jhjkh
B A hjghj

and following output in another query the hierarky in which mai is been forwarded
A
B
C
D
B
A

Plz help me

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-27 : 12:37:49

The second bit

select [From] from
(select [From], Fid from forwarded where Mid = 1
union all
select top 1 [To], Fid+1 from forwarded where Mid = 1 order by Fid desc
) a
order by Fid

The first bit depends on what format you want the result.
3 resultsets? 1 resultset with unused columns? 1 resultset with duplicated values? 2 output parameters and a resultset?

for the last

create proc s_GetMail
@Mid int ,
@subject varchar(100) out ,
@content varchar(1000) out
as
select @subject = subject, @content = content from MainMail where Mid = @Mid
select [From], [To], Remark from forwarded where Mid = @Mid order by Fid
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources