IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid INT NOT NULL,
mgrid INT NULL,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL
)
GO
IF OBJECT_ID('Departments') IS NOT NULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT
)
GO
-- fill datas
INSERT INTO employees VALUES (1,NULL,'Nancy',00.00)
INSERT INTO employees VALUES (2,1,'Andrew',00.00)
INSERT INTO employees VALUES (3,1,'Janet',00.00)
INSERT INTO employees VALUES (4,1,'Margaret',00.00)
INSERT INTO employees VALUES (5,2,'Steven',00.00)
INSERT INTO employees VALUES (6,2,'Michael',00.00)
INSERT INTO employees VALUES (7,3,'Robert',00.00)
INSERT INTO employees VALUES (8,3,'Laura',00.00)
INSERT INTO employees VALUES (9,3,'Ann',00.00)
INSERT INTO employees VALUES (10,4,'Ina',00.00)
INSERT INTO employees VALUES (11,7,'David',00.00)
INSERT INTO employees VALUES (12,7,'Ron',00.00)
INSERT INTO employees VALUES (13,7,'Dan',00.00)
INSERT INTO employees VALUES (14,11,'James',00.00)
INSERT INTO departments VALUES (1,'HR',2)
INSERT INTO departments VALUES (2,'Marketing',7)
INSERT INTO departments VALUES (3,'Finance',8)
INSERT INTO departments VALUES (4,'R&D',9)
INSERT INTO departments VALUES (5,'Training',4)
INSERT INTO departments VALUES (6,'Gardening',NULL)
GO
--SELECT * FROM departments
-- table-value function
IF OBJECT_ID('fn_getsubtree') IS NOT NULL
DROP FUNCTION fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)
RETURNS TABLE
AS
RETURN(
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM employees
WHERE empid = @empid
UNION ALL
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM employees AS e
join employees_subtree AS es
ON e.mgrid = es.empid
)
SELECT * FROM Employees_Subtree
)
GO
SELECT * FROM dbo.Departments
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST CROSS join employees
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid INT NOT NULL,
mgrid INT NULL,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL
)
GO
IF OBJECT_ID('Departments') IS NOT NULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT
)
GO
-- fill datas
INSERT INTO employees VALUES (1,NULL,'Nancy',00.00)
INSERT INTO employees VALUES (2,1,'Andrew',00.00)
INSERT INTO employees VALUES (3,1,'Janet',00.00)
INSERT INTO employees VALUES (4,1,'Margaret',00.00)
INSERT INTO employees VALUES (5,2,'Steven',00.00)
INSERT INTO employees VALUES (6,2,'Michael',00.00)
INSERT INTO employees VALUES (7,3,'Robert',00.00)
INSERT INTO employees VALUES (8,3,'Laura',00.00)
INSERT INTO employees VALUES (9,3,'Ann',00.00)
INSERT INTO employees VALUES (10,4,'Ina',00.00)
INSERT INTO employees VALUES (11,7,'David',00.00)
INSERT INTO employees VALUES (12,7,'Ron',00.00)
INSERT INTO employees VALUES (13,7,'Dan',00.00)
INSERT INTO employees VALUES (14,11,'James',00.00)
INSERT INTO departments VALUES (1,'HR',2)
INSERT INTO departments VALUES (2,'Marketing',7)
INSERT INTO departments VALUES (3,'Finance',8)
INSERT INTO departments VALUES (4,'R&D',9)
INSERT INTO departments VALUES (5,'Training',4)
INSERT INTO departments VALUES (6,'Gardening',NULL)
GO
--SELECT * FROM departments
-- table-value function
IF OBJECT_ID('fn_getsubtree') IS NOT NULL
DROP FUNCTION fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)
RETURNS TABLE
AS
RETURN(
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM employees
WHERE empid = @empid
UNION ALL
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM employees AS e
join employees_subtree AS es
ON e.mgrid = es.empid
)
SELECT * FROM Employees_Subtree
)
GO
SELECT * FROM dbo.Departments
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST CROSS join employees