Solved

Stored procedure...

Posted on 2009-04-03
9
223 Views
Last Modified: 2013-12-17
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


0
Comment
Question by:johny_bravo1
  • 5
  • 3
9 Comments
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24058306
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
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058340
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
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058347
I want to add receiverid of type T,C,B in a sepearate column but in a single row for that MId
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24058436
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
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058470
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
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058479
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
0
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24058606

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

sorry but i not understand what you mean
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058622
There is one row of type 'T'
two rows of type 'C'
1 row of type 'B'

0
 
LVL 5

Accepted Solution

by:
kosturdur earned 500 total points
ID: 24059045
Hi johny,
You could use cursors to do that.

By using Cursors;
first collect all Type 'T's and where mid= 1 and set a variable called @T
second collect all Type 'C's and where mid= 1 and set a variable @C
third collect all Type 'B's and where mid= 1 and set a variable @B
then made a select;

select senderid, subject, mailbody,attach1,attach2,attach3,attach4,attach5,status,
@T, @B, @C from tmsmailinfo

The sample of using a cursor is like;(just a sample not the one you need)

***************CODE BEGINS*********************
DECLARE mycursor CURSOR FOR
SELECT     lookup.UserID, users.[User], lookup.EMailID,emails.EMailName
FROM       lookup INNER JOIN
                      emails ON lookup.EMailID = emails.EMailID INNER JOIN
                      users ON lookup.UserID = users.UserID
GROUP BY lookup.UserID, users.[User], lookup.EMailID, emails.EMailName


OPEN mycursor

DECLARE @LastUserID int
DECLARE @CurrentUserID int
DECLARE @CurrentUser varchar(20)
DECLARE @CurrentEMailID int
DECLARE @EMailType varchar(100)
DECLARE @CurrentEMailType varchar(100)
DECLARE @CurrentEMailName varchar(50)

FETCH NEXT FROM mycursor INTO @CurrentUserID, @CurrentUser, @CurrentEMailID, @CurrentEMailName
SET @LastUserID = @CurrentUserID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
      IF (@LastUserID = @CurrentUserID)
      BEGIN
        SET @EMailType = (CASE WHEN @EMailType is NULL THEN @CurrentEMailName ELSE @EMailType +' ; '+ @CurrentEMailName END)
      END
      ELSE
      BEGIN
        SELECT  @CurrentUserID, @CurrentUser, @EMailType --Selects
        SET @LastUserID = @CurrentUserID
        SET @EMailType = @CurrentEMailName
      END
    END
    FETCH NEXT FROM mycursor INTO @CurrentUserID, @CurrentUser, @CurrentEMailID, @CurrentEMailName
    SELECT @CurrentUserID, @CurrentUser, @EMailType -- Selects
END
CLOSE mycursor
DEALLOCATE mycursor
Go
***********CODE ENDS*********
HTH,
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
compare between entities in entity framework 8 74
How do I subtract date and time within a same column in SQL 4 38
SQL Help 27 46
SQL Error - Query 6 26
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question