Using Inner Join on Comma-Separated Values in Stored Procedure

Question

I have a table UserMaster as follow...(only required columns are shown)

UserID UserName EmailID
---------------------------------
1000   amol     [email protected]
1001   mahesh   [email protected]
1002   saurabh  [email protected]
1003   nitesh   [email protected]

Another table MessageHistory (Only required columns are shown)

MsgCode From  To
-----------------------------
MSG001  1000  1001,1002,1003
MSG002  1001  1000,1002,1003

I am storing UserIds in From and To columns...

I am trying to create a stored procedure to display the Email History of particular message code

Create Procedure proc_GetMessageHistory
        @MsgCode varchar(50)
        as
        Begin
           Select * From MessageHistory Where MsgCode=@MsgCode 
        End

The result is coming as shown above in MessageHistory table...but I want to show respective UserEmailIDs instead of UserID (e.g. '[email protected]' instead of 1000)...

How could I do this in a stored procedure? How could I use inner join in this case specially with comma-separated values? Please help...thanks

DO NOT use comma-separated values - that's horribly bad design and always will be a mess and an awful hack to use. You need to use properly normalized database tables - then a JOIN will be a no problem at all... — marc_s
– marc_s, Commented Mar 17, 2013 at 9:11
Yes i agree..that will be the very final solution for this to change DB structure...but isn't there any way to achieve this without doing that? — Amol Kolekar
– Amol Kolekar, Commented Mar 17, 2013 at 9:19
There are some god-awful, crazy messy hacks - I'd rather not think about those, though ..... (shudder) — marc_s
– marc_s, Commented Mar 17, 2013 at 9:35
You would need to implement the equivalent of string.Split as a UDF, join against the output of that, and then join to UserMaster. You can do it but it would be very slow. — Phil
– Phil, Commented Mar 17, 2013 at 9:38

Ian Preston · Accepted Answer · 2013-03-17 13:29:33Z

1

As everyone has already noted, this should never be any sort of permanent solution, as there no way it will ever perform in an efficient manner. Also, that sort of denormalised structure is likely to have any number of issues. That said...

List of email addresses per messages, i.e. one recipient per row:

select m.MsgCode
  , sender = s.EmailID
  , recipient = u.EmailID
from MessageHistory m
  inner join UserMaster s on m.[From] = s.UserID
  inner join UserMaster u on charindex(cast(u.UserID as varchar), m.[To]) > 0

SQL Fiddle with demo.

List of messages, comma separated list of email addresses, one message per row:

with emails as
(
  select m.MsgCode
    , recipient = u.EmailID
  from MessageHistory m
    inner join UserMaster u on charindex(cast(u.UserID as varchar), m.[To]) > 0
)
select m.MsgCode
  , [From] = u.EmailID
  , [To] = stuff
    (
      (
        select ',' + recipient
        from emails e
        where m.MsgCode = e.MsgCode
        for xml path('')
      )
      , 1
      , 1
      , ''
    )
from MessageHistory m
  inner join UserMaster u on m.[From] = u.UserID

SQL Fiddle with demo.

edited Mar 17, 2013 at 13:29

answered Mar 17, 2013 at 12:11

Ian Preston

39.8k9 gold badges98 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amol Kolekar Over a year ago

After above comments i have changed the structure of DB ...though i will try to understand and implement this code...its new learning to me...thank you for your great effort...+1 :)

Collectives™ on Stack Overflow

Using Inner Join on Comma-Separated Values in Stored Procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related