עבודה עם טבלאות היררכיות

CREATE TABLE [dbo].[employees](
	[employee_id] [int]  NOT NULL,
	[First_Name] [varchar](25) NULL,
	[Last_Name] [varchar](25) NULL,
	[manager_id] [int] NULL
)
GO

INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (1, N'Alvin', N'Smith', NULL)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (2, N'Jose', N'Jones', 1)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (3, N'Amado', N'Taylor', 1)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (4, N'Stuart', N'Williams', 1)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (5, N'Demarcus', N'Brown', 2)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (6, N'Mark', N'Davies', 2)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (7, N'Merlin', N'Evans', 2)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (8, N'Elroy', N'Wilson', 7)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (9, N'Charles', N'Thomas', 7)
INSERT [dbo].[employees] ([employee_id], [First_Name], [Last_Name], [manager_id]) VALUES (10, N'Rudolph', N'Roberts', 7)

 --Q1--
 
SELECT E1.First_Name AS EmpName, E2.First_Name AS MangerName
FROM employees E1, employees E2
WHERE E1.manager_id= E2.employee_id

--Q2—
 
--SOL1 TSQL --
CREATE FUNCTION DBO.GetManagerHierarchy1 (@EmpIDInput int)
RETURNS @ManagerHierarchy Table (EmpID int, FirstName varchar(45), ManagerId int)
AS BEGIN
DECLARE  @EmpID int, @ManagerID int, @EmpName varchar(45) 
SET @EmpID=@EmpIDInput
WHILE @EmpID IS NOT NULL
    BEGIN      
    SELECT @ManagerID =E1.manager_id, @EmpName = E1.First_Name
    FROM employees E1
    WHERE E1.employee_id = @EmpID
         
    INSERT INTO @ManagerHierarchy VALUES (@EmpID, @EmpName, @ManagerID )
 
    SET @EmpID = @ManagerID
    END
RETURN
END
--CHECK
SELECT * FROM DBO.GetManagerHierarchy1(7) 
SELECT * FROM DBO.GetManagerHierarchy1 (10)
 
 
--SOL2 SQL--
WITH ManagerHierarchyTable(EmpID,EmpFirstName,ManagerID)
AS
(
    SELECT e1.employee_id,e1.First_Name,e1.manager_id
    FROM employees e1
    WHERE e1.employee_id = 10
 
    UNION ALL
 
    SELECT e.employee_id, e.First_Name, e.manager_id
    FROM Employees e, ManagerHierarchyTable mht
    WHERE e.employee_id = mht.ManagerID
 
)
SELECT * FROM ManagerHierarchyTable