recursion programming

Question

recursion programming

Anamika

SSCertifiable

Points: 6202
More actions
March 29, 2010 at 3:24 am

#220463

Hi,
i have a table structure where id and childmemberid is there.
I want to list all the childmemberid's of all a member with their grandchildID's next level and next next levels.
ID ChildID
1 2
3
4
2 5
2 7
3 6
5 9
6 10
9 11
i want to list till the nth level for each parent level
CREATE TABLE [dbo].[TblBridgeMember](
[ID] [int] NOT NULL,
[ChildMemberId] [int] NOT NULL,
CONSTRAINT [IX_ID_MemberID] UNIQUE CLUSTERED
(
[ID] ASC,
[ChildMemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_MemberID] UNIQUE NONCLUSTERED
(
[ChildMemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[TblBridgeMember] VALUES (1,2)
INSERT INTO [dbo].[TblBridgeMember] VALUES (1,3)
INSERT INTO [dbo].[TblBridgeMember] VALUES (2,4)
INSERT INTO [dbo].[TblBridgeMember] VALUES (3,5)
INSERT INTO [dbo].[TblBridgeMember] VALUES (4,6)
INSERT INTO [dbo].[TblBridgeMember] VALUES (6,7)
INSERT INTO [dbo].[TblBridgeMember] VALUES (5,8)
INSERT INTO [dbo].[TblBridgeMember] VALUES (8,9)
INSERT INTO [dbo].[TblBridgeMember] VALUES (9,10)
EXPECTED OUTPUT.
When i query on id 1 i should get
LEVEL1 2,3
LEVEL2 4,5
LEVEL3 6,8
LEVEL4 7,9
LEVEL5 10
any thoughts / inputs are appreciated.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Anamika (3/29/2010)
EXPECTED OUTPUT.
When i query on id 1 i should get
LEVEL1 2,3
LEVEL2 4,5
LEVEL3 6,8
LEVEL4 7,9
LEVEL5 10

Confusing , can you put more light ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Anamika SSCertifiable Points: 6202 More actions · Answer 2

when i query for ID 1

in the first level it has got 2 and 3

then in the next level 4 and 5 - are children of 2 and 3

then in the next level 6 and 8 - are children of 4 and 5 and so on...

thanks for your reply

ami

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

what would be the result when ID = 2 ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150468 More actions · Answer 4

There are a number of alternatives, with examples and code, here:

http://consultingblogs.emc.com/christianwade/archive/2004/11/09/234.aspx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Anamika SSCertifiable Points: 6202 More actions · Answer 5

There are a number of alternatives, with examples and code, here:

http://consultingblogs.emc.com/christianwade/archive/2004/11/09/234.aspx

The link was highly useful.

Thanks for sharing,

Ami