[SQL] Finding the Boss with Hierarchical Queries
Never thought I was going to blog about SQL... but I'm doing this mostly for myself because I need to use this at work on monday. I know enough to do database design and whip up a few necessary stored procs, but when it comes to functions and the new Common Table Expression (CTE) in SQL 2005 I'm quite lost. I got a tip about CTE from my buddy Jan-Erik so I sat down with Google and SQL Books online to see if it could help me out.
Most examples out there lists the employees below a manager, and their employess etc. What I needed to do was go the other way - to create a view or something which showed all the employees in a table and who their manager on a certain level is. Not just their immediate boss, but the boss on some upper level.
To test this I started out with a small, simple table called Employee with 4 columns in it (EmpId, Name, BossId, Type), where BossID is the immediate boss, or the "parent":
CREATE TABLE [dbo].[Employee](
TABLE [dbo].[Employee](
[EmpId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[BossId] [int] NULL,
[Type] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
Or something like that J I'm also setting a FK between BossId and EmpId:
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([BossId])
REFERENCES [dbo].[Employee] ([EmpId])
Next I filled it with some data:
1 Helen NULL Mega boss
2 Lisa 1 Boss
3 Otto 1 Boss
4 Carin 2 Team lead
5 Carl 2 Team lead
6 Niel 4 Project manager
7 Thomas 3 Team lead
8 Johan 4 Code monkey
Pretty simple, but enough to test with. This organization has 4 levels, from 'Mega boss' to 'Code monkey'. To iterate "up" in the hierarchy I'm using CTE and the WITH <table(columns)> AS syntax, and I'm putting it into a scalar function which takes the EmpID and the manager Type as parameters:
CREATE FUNCTION [dbo].[GetBossByType]
(
@empid int,
@type varchar(20)
)
RETURNS int
AS
BEGIN
DECLARE @bossid int;
WITH reports(EmpId,name,bossid,type) AS
(
-- set the starting point, a specific employee (from in-parameter)
SELECT EmpiD,name,bossid,type FROM Employee WHERE empid = @empid
UNION ALL
-- get the empid from the person (e) who has empid which corresponds the the previous person's (r) bossid
SELECT e.empid,e.name,e.bossid,e.type FROM employee e JOIN reports r
ON r.bossid = e.empid WHERE r.bossid IS NOT NULL
)
-- from the above we have a hierarchical list of managers in a table called "reports",
-- get the id from the boss with the type I'm looking for
SELECT @bossid = empid FROM reports WHERE TYPE = @type;
RETURN @bossid
END
Now, to create a view that has empid, name and boss id for a specific "level" I used a SELECT statement like this:
SELECT EmpId, Name, dbo.GetBossByType(EmpId, 'boss') AS BossId
FROM dbo.Employee
WHERE (BossId IS NOT NULL)
As you can see, I'm calling my function (GetBossByType) as part of the SELECT, passing in the EmpID and the type of manager I want to list. The result of the query/view is this (EmpId, Name, BossId):
2 Lisa 2
3 Otto 3
4 Carin 2
5 Carl 2
6 Niel 2
7 Thomas 3
8 Johan 2
Works for me. Lisa and Otto are of the manager type 'Boss' already, that's why they have their own EmpId as BossId. The hierarchical query can be a bit difficult to grasp first, but it's powerful. Sorry about the bad syntax and not sticking to upper/lower cases everywhere :)