June 30, 2012 at 2:14 am
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL)
INSERT INTO [tblMembersRelation] ([MemberID],[ParentID])
VALUES (1, null ), (2, 1), (3, 1), (4, 2), (5, 3), (6, 4)
when we give select * from [tblMembersRelation]
MembersRelationIDMemberID ParentID
1 1 NULL
2 2 1
3 3 1
4 4 2
5 5 3
6 6 4
in a select query if i pass 6(memberId) i need to get the parentId for that memberId.
so i used the below query.,
select ParentID from tblMembersRelation where MemberID =6
The output comes like,
ParentId
=======
4
Now., i need to pass the output as memberId as input and get the parentId for the input..
For ex., If i pass 6 the parentId is 4 then i need pass the output automatically and it should get the parentId for 4 so the output is 2 then
the 2 should pass as memberId then the output should comes as 1. same as i need to pass those value in a loop.
So how can i do that?
June 30, 2012 at 3:14 am
First, I thought you should know I used BOL to help me write the following code. I hope it helps you with what you are trying to do.
/****** Object: Table [dbo].[tblMembersRelation] Script Date: 06/30/2012 03:10:35 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMembersRelation]') AND type in (N'U'))
DROP TABLE [dbo].[tblMembersRelation]
GO
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL);
GO
INSERT INTO [tblMembersRelation] ([MemberID],[ParentID])
VALUES (1, null ), (2, 1), (3, 1), (4, 2), (5, 3), (6, 4);
GO
-- Show all rows in source table
select * from dbo.[tblMembersRelation];
GO
-- Show the hierarchy of the rows in the source table
with Members (
MemberID,
ParentID,
MemberLevel
) as (
select
MemberID,
ParentID,
1 as MemberLevel
from
[dbo].[tblMembersRelation]
where
ParentID is null
union all
select
mr.MemberID,
mr.ParentID,
m.MemberLevel + 1
from
[dbo].[tblMembersRelation] mr
inner join Members m
on (mr.ParentID = m.MemberID)
)
select
MemberID,
ParentID,
MemberLevel
from
Members
order by
ParentID;
GO
-- Given a specific MemberID show that member and all members above the member
declare @SearchMember int = 6;
with Members (MemberID, ParentID)
as (
select
MemberID,
ParentID
from
[dbo].[tblMembersRelation]
where
MemberID = @SearchMember
union all
select
mr.MemberID,
mr.ParentID
from
[dbo].[tblMembersRelation] mr
inner join Members m
on (mr.MemberID = m.ParentID)
)
select
MemberID,
ParentID
from
Members
order by
MemberID desc;
GO
June 30, 2012 at 7:48 am
thanks lynn pettis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply