I have the following 2 tables:
CREATE TABLE [Names]
(
[Id] INT PRIMARY KEY,
[Name] VARCHAR(100)
)
CREATE TABLE [Relationships]
(
[Parent] [int] REFERENCES [Names]([Id]),
[Child] [int] REFERENCES [Names]([Id])
)
Sample Data:
INSERT [NAMES] VALUES (1,'FRANK')
INSERT [NAMES] VALUES (2,'JO')
INSERT [NAMES] VALUES (3,'MARY')
INSERT [NAMES] VALUES (4,'PETER')
INSERT [NAMES] VALUES (5,'MAY')
INSERT [RELATIONSHIPS] VALUES (1,2)
INSERT [RELATIONSHIPS] VALUES (2,3)
INSERT [RELATIONSHIPS] VALUES (4,2)
INSERT [RELATIONSHIPS] VALUES (5,4)
How to show a nested (tree) list of names including [Id], [Name] and [Level], where [Level] indicates the nest level from the top (Root: Level = 0; First children of Root: Level = 1; etc…)? For instance, the result should be showing:
Level Relationship
----- ------------
2 FRANK <- JO
3 FRANK <- JO <- MARY
2 PETER <- JO
3 MAY <- PETER <- JO
2条答案
按热度按时间e1xvtsh31#
You might consider switching to Hierarchical Data . TSQL supports it well enough and you don't need to 'reinvent the wheel'. This will make you queries easier in the long run.
Go here for a nice tutorial on the subject.
nhaq1z212#
Try this: