Loop an SQL SELECT statement

I've got a table to hold forum posts for our support section of our website:

CREATE TABLE [dbo].[tblCommunity](
[CounterID] [bigint] IDENTITY(1,1) NOT NULL,
[Platform] [varchar](100) NOT NULL,
[DateTime] [datetime] NOT NULL CONSTRAINT [DF_tblCommunity_DateTime]
DEFAULT (getdate()),
[ParentThreadID] [bigint] NULL,
[Category] [int] NOT NULL,
[PostedBy] [nvarchar](255) NOT NULL,
[MessagePlainText] [nvarchar](4000) NOT NULL,
[MessageSubject] [nvarchar](4000) NOT NULL,
[GroupLanguage] [nvarchar](200) NOT NULL,
[IDKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblCommunity_IDKey]
DEFAULT (newid()),
[NotifyMeOfReplies] [bit] NULL,
CONSTRAINT [PK_tblCommunity] PRIMARY KEY CLUSTERED
(
[CounterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


If there are new threads, the ParentIDThread is NULL.

If someone is replying to an existing thread, the ParentIDThread field holds
the CounterID of the original thread.

I want a new stored procedure to return me a list of all [PostedBy] values
(these are actually email addresses) of all upstream people for a particular
[CounterID].

That way with that list I can BCC email (with ASP.NET) all of those people
any responses to the thread they were involved on. The list has to have all
duplicate [PostedBy] values removed too so people aren't emailed twice.

E.g. Input parameter @EnterCounterID: 457
Output:

345
310
288
287
158

What would be the SELECT SQL to accomplish that?

 

Whoops wrong newsgroup. Should be microsoft.public.sqlserver.programming.

Apologies.

"Mark B" <none123@none.com> wrote in message
news:ujPda%23RzJHA.3404@TK2MSFTNGP02.phx.gbl...
> I've got a table to hold forum posts for our support section of our
> website:
>
> CREATE TABLE [dbo].[tblCommunity](
> [CounterID] [bigint] IDENTITY(1,1) NOT NULL,
> [Platform] [varchar](100) NOT NULL,
> [DateTime] [datetime] NOT NULL CONSTRAINT [DF_tblCommunity_DateTime]
> DEFAULT (getdate()),
> [ParentThreadID] [bigint] NULL,
> [Category] [int] NOT NULL,
> [PostedBy] [nvarchar](255) NOT NULL,
> [MessagePlainText] [nvarchar](4000) NOT NULL,
> [MessageSubject] [nvarchar](4000) NOT NULL,
> [GroupLanguage] [nvarchar](200) NOT NULL,
> [IDKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblCommunity_IDKey]
> DEFAULT (newid()),
> [NotifyMeOfReplies] [bit] NULL,
> CONSTRAINT [PK_tblCommunity] PRIMARY KEY CLUSTERED
> (
> [CounterID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> If there are new threads, the ParentIDThread is NULL.
>
> If someone is replying to an existing thread, the ParentIDThread field
> holds the CounterID of the original thread.
>
> I want a new stored procedure to return me a list of all [PostedBy] values
> (these are actually email addresses) of all upstream people for a
> particular [CounterID].
>
> That way with that list I can BCC email (with ASP.NET) all of those people
> any responses to the thread they were involved on. The list has to have
> all duplicate [PostedBy] values removed too so people aren't emailed
> twice.
>
> E.g. Input parameter @EnterCounterID: 457
> Output:
>
> 345
> 310
> 288
> 287
> 158
>
> What would be the SELECT SQL to accomplish that?
>
>
>
>
>
>
>
>
>
>
>