(MySQL) How to join two columns in one table and display results?

Question

I have one table where are columns employeeNumber and also reportsTo as you can see from the screenshot. Every name reports to another name. Like employeeNumber 1165 reportsTo 1143.

How can I display every firstName and lastName of employee and also firstName and lastName of his "boss" in the results?

I hope you understand me.

Answer 1

You can try this :

SELECT e.firstName, e.lastName, b.firstName, b.lastName
FROM employees as e
LEFT JOIN employees as b on b.employeeNumber = e.reportsTo;

1. You select firtName and lastName of each employee
2. You select firstName and lastName of their boss
3. Using left join should return some NULL value for boss that report to no one
4. I did the join on b.employeeNumber = e.reportsTo so you will get boss information from the employeeNumber where the employee report to (eg. employee 1056 report to employee 1002 so employee data are Patterson Mary and he reports to employee Murphy Diane, her boss)

---

EDIT :

After reading Hamza Haider answer about SELF JOIN (name of what I did before with the join on same table) I find this other question where the answer looks like what you want and it will give you some information to understand how SELF JOIN works : What is SELF JOIN and when would you use it?

According to the answer, here is an other solution :

SELECT 
    e1.FirstName, 
    e1.LastName,
    e2.FirstName as SupervisorFirstName, 
    e2.LastName as SupervisorLastName
FROM employee e1
LEFT OUTER JOIN employee e2 on e2.employeeNumber = e1.reportsTo

The difference is the LEFT OUTER JOIN and not LEFT JOIN but they are both same (Are left outer joins and left joins the same?)

Answer 2

try with this one. It will return only those which has reportsTo value (not null value).

SELECT 
    e.firstName,
    e.lastName,
    r.firstName AS bossFirstName,
    r.lastName AS bossLastName
FROM employees AS e
INNER JOIN employees AS r ON e.employeeNumber = r.reportsTo;

If you want to see every employee even if it doesn't have reportsTo value, you may use LEFT JOIN:

SELECT 
    e.firstName,
    e.lastName,
    r.firstName AS bossFirstName,
    r.lastName AS bossLastName
FROM employees AS e
LEFT JOIN employees AS r ON e.employeeNumber = r.reportsTo;

Answer 3

You need to use self join.Self Join

select * from Employees AS Employees1  join Employees  as Employees2 ON Employees1.employeeNumber = Employees2.reportsTo