Let's have the following example:
We have a table with and Id and ParentId hold relations between employees and we want to see all employees that are under a manager or team leader.
First the sql table and data
We have a table with and Id and ParentId hold relations between employees and we want to see all employees that are under a manager or team leader.
First the sql table and data
CREATE TABLE [dbo].[TreeEmployees](
[Id] [bigint] NOT NULL,
[ParentId] [bigint] NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](200) NOT NULL,
[Position] [varchar](50) NOT NULL,
CONSTRAINT [PK_Tree] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TreeEmployees] ADD CONSTRAINT [DF_Tree_Name] DEFAULT ('') FOR [Name]
GO
ALTER TABLE [dbo].[TreeEmployees] ADD CONSTRAINT [DF_Tree_Description] DEFAULT ('') FOR [Description]
GO
ALTER TABLE [dbo].[TreeEmployees] ADD CONSTRAINT [DF_Tree_Position] DEFAULT ('') FOR [Position]
GO
Add sql data
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (1, NULL, N'John Doe', N'John folowers', N'Technical Manager')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (2, NULL, N'Jan Right', N'Jan folowers', N'Chief Account manager')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (3, 1, N'Martin Flynn', N'Flyn team', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (4, 1, N'Joyce May Ann', N'Joyce team', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (5, 3, N'Mia Donovan', N'Meber of Fylnn''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (6, 3, N'Laurel Bean', N'Member of Flynn''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (7, 4, N'Anna Mendez', N'Member of Joyce''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (8, 4, N'Maria Fernandez', N'Member of Joyce''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (9, 2, N'Carter Bowls', N'Jan''s team 1', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (10, 2, N'Jimmy Clarck', N'Jan''s team 2', N'Team Leader')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (11, 9, N'Sully Westwood', N'Carter''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (12, 9, N'Louise Senna', N'Carter''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (13, 10, N'Lilly Ann Parker', N'Jimmy''s team', N'Worker')
INSERT [dbo].[TreeEmployees] ([Id], [ParentId], [Name], [Description], [Position]) VALUES (14, 10, N'Tina Riggs', N'Jimmy''s team', N'Worker')
And here is the recursive query:
declare @rootId bigint
--set this to 1 or 2
set @rootId = 2;
WITH TeamMembers as
(
select Id, ParentId, Name, [Description],[Position], 0 as [level] from TreeEmployees
where Id=@rootId
union all
select tc.id, tc.ParentId, tc.Name, tc.[Description],tc.[Position],[level] +1 as [level] from TreeEmployees as tc
inner join TeamMembers on tc.ParentId = TeamMembers.Id
)
select * from TeamMembers
And here are the results:
Id | ParentId | Name | Description | Position | level |
2 | NULL | Jan Right | Jan folowers | Chief Account manager | 0 |
9 | 2 | Carter Bowls | Jan's team 1 | Team Leader | 1 |
10 | 2 | Jimmy Clarck | Jan's team 2 | Team Leader | 1 |
13 | 10 | Lilly Ann Parker | Jimmy's team | Worker | 2 |
14 | 10 | Tina Riggs | Jimmy's team | Worker | 2 |
11 | 9 | Sully Westwood | Carter's team | Worker | 2 |
12 | 9 | Louise Senna | Carter's team | Worker | 2 |