June 11, 2012 at 2:51 pm
here iam having two tables from this two table i just want to find hir
CREATE TABLE [dbo].[tblMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL
)
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL
)
for example i am inserting datas
INSERT INTO [countrylattitude].[dbo].[tblMembers]
([Name])
VALUES
('siva' union all,
'ganesh' union all,
'rajesh' union all,
'gobi' union all,
'arun' union all,
'rajesharavind'
)
GO
INSERT INTO [countrylattitude].[dbo].[tblMembersRelation]
([MemberID]
,[ParentID]
)
VALUES
(1
,null
)
slowly i am just inserting will be
the table select * from table 1 and table
tblMember
MemberIdName
1Siva
2Ganesh
3rajesh
4Gopi
5arun
6rajesharavind
tblMemberRelation
MemberRelationIdMemberIdParentMemberId
110
221
331
44 2
55 3
664
from this two table i just want to find Hierarchy Node
FindHirerachy1
LevelsMemberIdNameParentMemberId
12Ganesh1
13rajesh 1
2 4 Gopi 2
2 5 arun 3
3 6 rajesharavind 2
i just tried this by joining two table'
select* tblmember
inner join tblMembersRelation n on n.member=tbl member
where parmeter=@parmeter
plz tell me how to make that
June 11, 2012 at 3:03 pm
Great job posting ddl. The sample data is too sparse for the second table to be able to provide much help. Also I am pretty uncertain what you want for output. Post a few more rows of the hierarchy data and explain what you want for output and we can knock this out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2012 at 4:00 pm
This solution is based on the Microsoft article: http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx
Here is the revised DDL to set up the scenario:
CREATE TABLE [dbo].[tblMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL
)
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL
)
INSERT INTO [tblMembers] ([Name])
VALUES('siva'),
('ganesh'),
('rajesh'),
('gobi'),
('arun'),
('rajesharavind')
INSERT INTO [tblMembersRelation] ([MemberID],[ParentID])
VALUES(1 ,null ),
(2, 1),
(3, 1),
(4, 2),
(5, 3),
(6, 4)
Here is one solution.
DECLARE@MemberID INTEGER = 1
;WITHMemberAndParent
AS(
SELECTm.MemberID, m.Name AS MemberName, r.ParentID
FROMtblMembers m
LEFT JOIN tblMembersRelation r
ONr.MemberID = m.MemberID
),
Hierarchy
AS(
SELECTParentID, MemberID, 1 AS LevelNum, MemberName, CAST(MemberName AS VARCHAR(1000)) AS SortOrder
FROMMemberAndParent
WHEREMemberID = @MemberID
UNIONALL
SELECTp.ParentID, p.MemberID, LevelNum + 1 AS LevelNum, p.MemberName, CAST(h.SortOrder + '|' + p.MemberName AS VARCHAR(1000)) AS SortOrder
FROMMemberAndParent p
JOINHierarchy h
ONh.MemberID = p.ParentID
)
SELECTLevelNum,
MemberID,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName
FROMHierarchy
ORDER BY SortOrder
;
Here is the output:
LevelNum MemberID MemberName
----------- ----------- ----------------------------------------------------------------------------------------------------
1 1 siva
2 2 | ganesh
3 4 | | gobi
4 6 | | | rajesharavind
2 3 | rajesh
3 5 | | arun
(6 row(s) affected)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply