Query to get employee name and manager name that are from different tables-CodePudding

Employee Table

EmployeeNumber (PK)
PersonID 
ReportstoManagerEmployeeNumber (i.e.- the employee number of the employee's manager)

Names Table

PersonID (PK) 
FirstName 
LastName

I want to display FirstName, LastName, EmployeeNumber, ReportstoManagerEmployeeNumber , Firstname as managerfirstname, LastName as managerlastname

Basically I want to select the first name and last name of the ReportstoManagerEmployeeNumber column

What I have tried:

SELECT n.FirstName, n.LastName, emp.EmployeeNumber, 
    emp.ReportstoManagerEmployeeNumber, n.firstname as managerfirstname, n.lastname as managerlastname
FROM Names n
INNER JOIN employees emp
    ON n.personID = emp.personID
INNER JOIN employees emp2
    ON n.personID = emp2.personID

I was thinking a self-join but this won't work as this just selects the names of the employees from the first and second column. I am new to SQL but I believe a subquery or a CTE is required but I am not sure how to set it up.

To clarify-- John Smith has personID = 1 in the Names table but has employeeID = 2 in the employee table.

CodePudding user response：

It is the other way around, you need to join 2 Names tables one for the employee and one for the manager

SELECT 
    emp.EmployeeNumber,
    n.FirstName,
    n.LastName,
    emp.ReportstoManagerEmployeeNumber,
    n1.firstname AS managerfirstname,
    n1.lastname AS managerlastname
FROM
    employees emp
        INNER JOIN
    Names n ON n.personID = emp.PersonID
        INNER JOIN
    employees  emp2 ON em2.EmployeeNumber= emp.ReportstoManagerEmployeeNumber
        INNEr JOIN
    Names n1 ON n1.personID = emp2.personID