asked on

Stored procedure...

Hi
I am a real beginner in SQL.I want to write a stored procedure.
I have two tables,tmsmailinfo and TMSMailDetails
tmsmailinfo has fields: ID,SenderID,Subject,MailBody
TMSMailDetails has fields: MId,ReceiverId
where MId is ID in tmsmailinfo.

e.g, TMSMailDetails has data
Mid ReceiverID Type
1      xyz@xyz.com      T
1      xyz3@xyz.com      C
1      xy2z@xyz.com      C
1      pqr@pqr.com      B
2      abc@abc.com      T
Here T stands for To,C for CC,B for BCC

tmsmailinfo has data
Id SenderId Subject MailBody
1      ts@t.com       TIMS update       Test mail
2      s@gmail.com TIMS update Test mail two

I want the output as

Id SenderId Subject MailBody ReciverId CC Bcc
1 ts@t.com      TIMS update       Test mail xyz@xyz.com xyz3@xyz.com,xyz2@xyz.com pqr@pqr.com

Daniel Junges

CREATE PROCEDURE selMailInfo
AS BEGIN
select i.id, i.SenderId, i.Subject, i.MailBody, d.ReciverId, d.CC, d.Bcc
from tmsmailinfo i, TMSMailDetails d
where i.id=d.Mid
END

Johny Bravo

ASKER

Thanks junges,
but that will not work.

What I am doing is

Create PROCEDURE GetMails
AS
select i.senderid,i.subject,i.mailbody,i.attach1,i.attach2,i.attach3,i.attach4,i.attach5,i.status,
d.receiverID,d.Type from tmsmailinfo i inner join TMSMailDetails d on i.ID=d.MId

What I want is, if the type is C,then those reciverId should be added into a new column as CC,same for B as BCC

Johny Bravo

ASKER

I want to add receiverid of type T,C,B in a sepearate column but in a single row for that MId

Daniel Junges

you can do the follow:

select i.senderid,i.subject,i.mailbody,i.attach1,i.attach2,i.attach3,i.attach4,i.attach5,i.status,
d.receiverID,d.Type,
CASE d.Type WHEN 'C' THEN d.receiverID ELSE null END as CC,
CASE d.Type WHEN 'B' THEN d.receiverID ELSE null END as BCC

from tmsmailinfo i inner join TMSMailDetails d on i.ID=d.MId

Johny Bravo

ASKER

Ya thats's ok.I think I am not able to give you what I want.

Suppose there is one row in tmsMailInfo with id=1,
in TMSMailDetails i may have 4 rows for the id 1,
let's say 1 for T,2 for C and 1 for B

I want the output in a single row for this data

Johny Bravo

ASKER

That id may confuse u
so
Suppose there is one row in tmsMailInfo with id=345,
in TMSMailDetails i may have 4 rows for the id 345,
let's say 1 for T,2 for C and 1 for B

I want the output in a single row for this data

Daniel Junges

"let's say 1 for T,2 for C and 1 for B"

sorry but i not understand what you mean

Johny Bravo

ASKER

There is one row of type 'T'
two rows of type 'C'
1 row of type 'B'

ASKER CERTIFIED SOLUTION

Umut Şeker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial