Below is my DDL. The table is self referencing.
The foreign key reference is bfhvT_ParentSID which references bfhvT_SID
---Start
CREATE TABLE [dbo].[tb_bfhvTest] (
    [bfhvT_SID] [int], -- THis IS MY IDENTITY (The Real table has an
Identity Column .....I Promise)
    [bfhvT_ParentSID] [int] NULL ,
    [bfhvT_b_SID] [int] NULL ,
    [bfhvT_Description] [varchar] (255)  NULL)


insert into tb_bfhvTest
    values(11356,NULL,3461,'Opt Out')
insert into tb_bfhvTest
    values(11357,NULL,3461,'Employee 1 Reference')
insert into tb_bfhvTest
    values(11358,11357,3461,'£40,000 Partner')
insert into tb_bfhvTest
    values(11359,11357,3461,'£60,000 Partner')
insert into tb_bfhvTest
    values(11360,11357,3461,'£100,000 Partner')
insert into tb_bfhvTest
    values(11361,NULL,3461,'Employee 2 Reference')
insert into tb_bfhvTest
    values(11362,11361,3461,'£40,000 Partner')
insert into tb_bfhvTest
    values(11363,11361,3461,'£60,000 Partner')
insert into tb_bfhvTest
    values(11364,11361,3461,'£100,000 Partner')

select * from tb_bfhvTest
--End
The resultset I am trying to achieve by using a select query and using
the
bfhvT_b_SID of 3641 in my where clause.
is :
bfhvT_SID | bfhvT_Description
11356      | 'Opt Out'
11357     | 'Employee 1 Reference'
11358      | 'Employee 1 Reference£40,000 Partner'
11359     | 'Employee 1 Reference£60,000 Partner'
11360     | 'Employee 1 Reference£100,000 Partner'
11361     | 'Employee 2 Reference'
11362     | 'Employee 2 Reference£40,000 Partner'
11363     | 'Employee 2 Reference£60,000 Partner'
11364     | 'Employee 2 Reference£100,000 Partner'

Thus Concatenating the 2 Descriptions if the parent has a child
and if not just have the original description.

Any help with this would be greatly appreciated.
Thanks in advance Paul

select t1.bfhvT_SID, t1.bfhvT_Description+coalesce(t2.bfhvT_Description,'')
from tb_bfhvTest t1
    left outer join tb_bfhvTest t2 on t1.bfhvT_SID=t2.bfhvT_ParentSID

hth,

dean

<Paul.Haw***@gmail.com> wrote in message
Below is my DDL. The table is self referencing.
The foreign key reference is bfhvT_ParentSID which references bfhvT_SID
---Start
CREATE TABLE [dbo].[tb_bfhvTest] (
[bfhvT_SID] [int], -- THis IS MY IDENTITY (The Real table has an
Identity Column .....I Promise)
[bfhvT_ParentSID] [int] NULL ,
[bfhvT_b_SID] [int] NULL ,
[bfhvT_Description] [varchar] (255)  NULL)


insert into tb_bfhvTest
values(11356,NULL,3461,'Opt Out')
insert into tb_bfhvTest
values(11357,NULL,3461,'Employee 1 Reference')
insert into tb_bfhvTest
values(11358,11357,3461,'£40,000 Partner')
insert into tb_bfhvTest
values(11359,11357,3461,'£60,000 Partner')
insert into tb_bfhvTest
values(11360,11357,3461,'£100,000 Partner')
insert into tb_bfhvTest
values(11361,NULL,3461,'Employee 2 Reference')
insert into tb_bfhvTest
values(11362,11361,3461,'£40,000 Partner')
insert into tb_bfhvTest
values(11363,11361,3461,'£60,000 Partner')
insert into tb_bfhvTest
values(11364,11361,3461,'£100,000 Partner')

select * from tb_bfhvTest
--End
The resultset I am trying to achieve by using a select query and using
the
bfhvT_b_SID of 3641 in my where clause.
is :
bfhvT_SID | bfhvT_Description
11356      | 'Opt Out'
11357     | 'Employee 1 Reference'
11358   | 'Employee 1 Reference£40,000 Partner'
11359     | 'Employee 1 Reference£60,000 Partner'
11360     | 'Employee 1 Reference£100,000 Partner'
11361     | 'Employee 2 Reference'
11362     | 'Employee 2 Reference£40,000 Partner'
11363     | 'Employee 2 Reference£60,000 Partner'
11364     | 'Employee 2 Reference£100,000 Partner'

Thus Concatenating the 2 Descriptions if the parent has a child
and if not just have the original description.

Any help with this would be greatly appreciated.
Thanks in advance Paul

Thanks Dean
But its not quite correct. The following is the returned resultset
--------------------------------------------------------------------------
11356    Opt Out
11357    Employee 1 Reference£40,000 Partner
11357    Employee 1 Reference£60,000 Partner
11357    Employee 1 Reference£100,000 Partner
11358    £40,000 Partner
11359    £60,000 Partner
11360    £100,000 Partner
11361    Employee 2 Reference£40,000 Partner
11361    Employee 2 Reference£60,000 Partner
11361    Employee 2 Reference£100,000 Partner
11362    £40,000 Partner
11363    £60,000 Partner
11364    £100,000 Partner
------------------------------------------------
Is there something else that can be done?

sorry, my bad. please try this one:

select t1.bfhvT_SID,
coalesce((select t2.bfhvT_Description from tb_bfhvTest t2 where
t2.bfhvT_SID=t1.bfhvT_ParentSID),'')+t1.bfhvT_Description
from tb_bfhvTest t1

dean

<Paul.Haw***@gmail.com> wrote in message
But its not quite correct. The following is the returned resultset
--------------------------------------------------------------------------
11356 Opt Out
11357 Employee 1 Reference£40,000 Partner
11357 Employee 1 Reference£60,000 Partner
11357 Employee 1 Reference£100,000 Partner
11358 £40,000 Partner
11359 £60,000 Partner
11360 £100,000 Partner
11361 Employee 2 Reference£40,000 Partner
11361 Employee 2 Reference£60,000 Partner
11361 Employee 2 Reference£100,000 Partner
11362 £40,000 Partner
11363 £60,000 Partner
11364 £100,000 Partner
------------------------------------------------
Is there something else that can be done?

Thanks Dean
PERFECT!

Cheers
Paul

I am assuming that this is for 2000, and if so and if you are constrained to
one level only, then the join concept works fine.  If the tree is of
unlimited depth, check this thread:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/aac2af1e42b9b045/8c0a57e8b6095c93?tvc=1#8c0a57e8b6095c93

I give a breadth first query solution that should get you started, and Celko
goes deeper into the tree concept with a more optimized solution that
requires more work ahead of time, though if your tree is quite short, the
breadth first approach is nice enough.

Below is my DDL. The table is self referencing.
The foreign key reference is bfhvT_ParentSID which references bfhvT_SID
---Start
CREATE TABLE [dbo].[tb_bfhvTest] (
[bfhvT_SID] [int], -- THis IS MY IDENTITY (The Real table has an
Identity Column .....I Promise)
[bfhvT_ParentSID] [int] NULL ,
[bfhvT_b_SID] [int] NULL ,
[bfhvT_Description] [varchar] (255)  NULL)


insert into tb_bfhvTest
values(11356,NULL,3461,'Opt Out')
insert into tb_bfhvTest
values(11357,NULL,3461,'Employee 1 Reference')
insert into tb_bfhvTest
values(11358,11357,3461,'£40,000 Partner')
insert into tb_bfhvTest
values(11359,11357,3461,'£60,000 Partner')
insert into tb_bfhvTest
values(11360,11357,3461,'£100,000 Partner')
insert into tb_bfhvTest
values(11361,NULL,3461,'Employee 2 Reference')
insert into tb_bfhvTest
values(11362,11361,3461,'£40,000 Partner')
insert into tb_bfhvTest
values(11363,11361,3461,'£60,000 Partner')
insert into tb_bfhvTest
values(11364,11361,3461,'£100,000 Partner')

select * from tb_bfhvTest
--End
The resultset I am trying to achieve by using a select query and using
the
bfhvT_b_SID of 3641 in my where clause.
is :
bfhvT_SID | bfhvT_Description
11356      | 'Opt Out'
11357     | 'Employee 1 Reference'
11358   | 'Employee 1 Reference£40,000 Partner'
11359     | 'Employee 1 Reference£60,000 Partner'
11360     | 'Employee 1 Reference£100,000 Partner'
11361     | 'Employee 2 Reference'
11362     | 'Employee 2 Reference£40,000 Partner'
11363     | 'Employee 2 Reference£60,000 Partner'
11364     | 'Employee 2 Reference£100,000 Partner'

Thus Concatenating the 2 Descriptions if the parent has a child
and if not just have the original description.

Any help with this would be greatly appreciated.
Thanks in advance Paul

Add a field
simple insert of strings
Complex Query
adjust my code
avoid using cursors....