I am trying to show the names of the bosses and their employees in charge in different columns.
I was able to do it with cross apply, but I was asked to do it without using it, but I can't really think out on how to do it.
I am using the Northwind database for SQL Server and this is my code:
CREATE PROCEDURE sp_getBossAndEmployeeNames
@bossID NCHAR(5)
AS
CREATE TABLE #TEMP_BOSSES_AND_EMPLOYEES
(
BossFullName VARCHAR(35),
EmployeeFullName VARCHAR(35)
)
INSERT INTO #TEMP_BOSSES_AND_EMPLOYEES
SELECT (FirstName + ' ' + LastName) as BossFullName, sub.EmployeeFullName
FROM Employees
CROSS APPLY
(
SELECT (FirstName + ' ' + LastName) as EmployeeFullName FROM Employees WHERE ReportsTo = @bossID
) AS sub
WHERE EmployeeID = @bossID
SELECT * FROM #TEMP_BOSSES_AND_EMPLOYEES
Just use an INNER JOIN
:
SELECT B.FirstName + ' ' + B.LastName AS BossFullName
, E.FirstName + ' ' + E.LastName AS EmployeeFullName
FROM dbo.Employees B
INNER JOIN dbo.Employees E ON E.ReportsTo = B.id
WHERE B.EmployeeID = @bossID;
You don't even need a temp table as far as I can see.