Select - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select

Author

Topic

gbastos
Starting Member

1 Post

Posted - 2008-07-15 : 09:40:53

Hi,

I have two tables within a SQL Server database; the first database has a column which is an identifier and another column which lists one of more email identifers for a second table, e.g.

ID EmailID
-- ----------

1 100
2 101,102,103,104
3 105,106

The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.

EmailID EmailAddress
--------- ------------------

100 address100@email.com
101 address101@email.com
102 address102@email.com
103 address103@email.com
104 address104@email.com
105 address105@email.com
106 address106@email.com

I need to create a stored procedure or function that:

1. Selects an EmailID from the first table, based on a valid ID,

2. ?Splits? the EmailID field of the first table (using the comma separator) so that there is an array of EmailIDs and then,

3. Selects the relevant EmailAddress value from the second table, based on a valid EmailID stored in the array

Is there any way that this can be done directly within SQL Server using a stored procedure/function, without having to select and separate the EmailIDs using a separate development language and then making repeated calls to the database in order to select the relevant EmailAddress?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 09:50:35

You shoud try normalise the table

Try this

Select 
	t1.Emailid, t2.EmailAddress 
from 
	table1 t1 inner join table2 t2 on ','+t1.Emailid+',' like '%,'+cast(t2.Emailid as varchar(5))+',%'

Madhivanan

Failing to plan is Planning to fail

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-07-15 : 20:06:22

gbastos,

Your schema seems turned inside out. It sounds like your first table holds some entity (people) that can have a set of emails associated. Your second table holds the email details.

Have you considered changing the schema so that all of the email information lies within the email table.

i.e.

create table entity (
id int,
... other entity information
)

create table Emails (
ParentID int,
EmailAddress varchar(256)
)

Now you can join the child to the parent using the parent's key

select p.id, p.entityname, c.EmailAddress
from entity p
inner join emails c
on p.id = c.ParentID

Put a foreign key on the child table so that you don't get dangling references and you're good to go...

=======================================
Some people walk in the rain, others just get wet. -Roger Miller, musician (1936-1992)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 20:39:43

see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

use CROSS APPLY with CSVTable or fnParseList

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources