sql-serverstored-proceduresnorthwind

Change this stored procedure to avoid using CROSS APPLY?


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

Solution

  • 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.