March 26, 2011

Sql 2005/2008 recursive query

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
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:

IdParentIdNameDescriptionPositionlevel
2NULLJan RightJan folowersChief Account manager0
92Carter BowlsJan's team 1Team Leader1
102Jimmy ClarckJan's team 2Team Leader1
1310Lilly Ann ParkerJimmy's teamWorker2
1410Tina RiggsJimmy's teamWorker2
119Sully WestwoodCarter's teamWorker2
129Louise SennaCarter's teamWorker2


kick it on DotNetKicks.com

No comments:

Post a Comment

Post a Comment