Question for T-SQL Gurus

SQL Apprentice Question
I have a table that relates emails together. I would like to leverage
this information to create a rollup key that can be used to identify
the entire group. Some groups will have one email some will have many.

Is there a way to approach this problem with a set based approach? I
know I can do it with a cursor but I prefer not to.


Please feel free to offer schema and/or modeling changes.


Thanks for looking!


CREATE TABLE dbo.email_key (
email_id INT identity
,email VARCHAR(100)
,rollup_id INT
)


CREATE TABLE dbo.email_email (
email_email_id INT identity
,email_id INT
,rel_email_id INT
)


CREATE TABLE dbo.rollup_key (
rollup_id INT identity
,primary_email_id INT)


TRUNCATE TABLE dbo.email_key
TRUNCATE TABLE dbo.email_email
TRUNCATE TABLE dbo.rollup_key


--SAMPLE DATA
INSERT INTO dbo.email_key (email) SELECT '...@abc.com'
INSERT INTO dbo.email_key (email) SELECT '...@abc.com'
INSERT INTO dbo.email_key (email) SELECT '...@abc.com'
INSERT INTO dbo.email_key (email) SELECT '...@abc.com'


--SAMPLE DATA
INSERT INTO dbo.email_email (email_id,rel_email_id) SELECT 1,2
INSERT INTO dbo.email_email (email_id,rel_email_id) SELECT 1,3
INSERT INTO dbo.email_email (email_id,rel_email_id) SELECT 2,1
INSERT INTO dbo.email_email (email_id,rel_email_id) SELECT 3,1
INSERT INTO dbo.email_email (email_id,rel_email_id) SELECT 2,3


--ONE POSSIBLE END RESULT
INSERT INTO dbo.rollup_key (primary_email_id) SELECT 1
INSERT INTO dbo.rollup_key (primary_email_id) SELECT 4
--RUN A COUPLE UPDATES TO SET email_key.rollup_id


Celko Answers
>> I have a table that relates emails together. I would like to leverage this information to create a rollup key that can be used to identify the entire group. <<


Roll ups are mathematical operations done on a hierachy. Google ROLLUP
and CUBE for details. Are you talking about building a tree structure
to show a newsgroup thread?


>> Is there a way to approach this problem with a set based approach? <<


Get a copy of TREES & HIERARCHIES IN SQL for several approaches for
this kind of problem.

You mgiht also want to get a basic data modeling book, so you will not
have table names that contain "key" as affix, will stop using IDENTITY
and will create tables with relational keys. Also, think about how bad
"email_email" is as a data element name. The proper syntax is "INSERT
INTO .. VALUES()", and not the proprietary SELECT you used.