Need Help in Sql Query joining three tables

Question

I have three tables as shown in the below format.

create table Information(InfoID int, TOEmailIDKey varchar(100),
      CCEmailIDKey varchar(100))

create table userdata(UserKey int, name varchar(50), EmailIDs varchar(100))

create table emailusers(EmailUserKey int, username varchar(100), 
       UsersMailIDs varchar(100))


insert into Information select 1,'100,101,102','201,202,203'

insert into userdata

select 201,'test','Test@test.com' union
select 202,'test2','Test2@test.com' UNION
select 203,'test3','Test3@test.com' 

insert into emailusers
select 100,'sample','sample@test.com' union
select 101,'sample2','sample2@test.com' union
select 102,'sample3','sample3@test.com' 
select * from Information
select * from userdata
select * from emailusers

from the above three tables i want output as below

TOMails(column1)                                       
sample@test.com,sample2@test.com,
sample3@test.com 

CCEmail (colum2)
Test.@test.com,Test2@test.com,Test3@test.com

Your table design needs to be changed. Why do you have three comma separated pk in information table ? — Erran Morad, May 23 '14 at 17:29
I agree with Borat, this is not a constructive table design. Nevertheless, what about your actual code to get your desired results? — timo.rieber, May 23 '14 at 17:47
insteading of chaning the tables schema can we get the output ? — Ravikumar_n, May 23 '14 at 17:54

score 1 · Accepted Answer · edited May 23 '17 at 12:20

Create a function like this:

CREATE FUNCTION dbo.splitstring (@stringToSplit varchar(MAX))
RETURNS 
@returnList TABLE ([Name] [nvarchar] (500))
AS 
BEGIN

DECLARE @name nvarchar(255)
DECLARE @pos INT

WHILE CHARINDEX (',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX (',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END 
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
GO

Then you are able to do:

DECLARE @string varchar(50)
DECLARE @tmp VARCHAR(250)
SET @string = (SELECT CCEmailIDKey FROM Information)
SET @tmp = ''

SELECT @tmp = @tmp + EmailIDs + ', ' 
FROM
(
    SELECT 
      u.EmailIDs 
    FROM 
      userdata u 
        JOIN dbo.splitstring(@string) s ON u.UserKey = s.name
) s

SELECT SUBSTRING(@tmp, 0, LEN(@tmp))
GO

DECLARE @string varchar(50)
DECLARE @tmp VARCHAR(250)
SET @string = (SELECT TOEmailIDKey FROM Information)
SET @tmp = ''

SELECT @tmp = @tmp + UsersMailIDs + ', ' 
FROM
(
    SELECT 
      u.UsersMailIDs 
    FROM 
      emailusers u 
        JOIN dbo.splitstring(@string) s ON u.EmailUserKey = s.name
) s

SELECT SUBSTRING(@tmp, 0, LEN(@tmp))
GO

What gives results just like you want, no matter your data changes.

SQL Fiddle

Combined this two answers:

Convert multiple rows into one with comma as separator

Tsql split string

currently i have only 3 email id later i mayhave n number of column at that how to make it work — Ravikumar_n, May 23 '14 at 18:47
You'll have to add more of `SUBSTRING`. As mentioned by others this is really not a good approach in table design. — AndLev, May 23 '14 at 18:50

Need Help in Sql Query joining three tables

1 Answers1