Can't find the supervisor name and title in the table!

Hello everyone,

I am on the lesson Intermediate Joins in SQL and screen # 5 Self-Joins, where I need to write queries about employee name and title and supervisor name and title.

My confusion is that I don’t see any column in the employee table that represents the supervisor’s first and last name and the title. But, running the query displays the information about the supervisor. How is it possible?

Here’s the query:

SELECT
    e1.first_name || " " || e1.last_name employee_name,
    e1.title employee_title,
    e2.first_name || " " || e2.last_name supervisor_name,
    e2.title supervisor_title
FROM employee e1
LEFT JOIN employee e2 ON e1.reports_to = e2.employee_id
ORDER BY 1;

Thanks for your time.

1 Like

Hello,

That’s because the supervisor him/herself is an employee, so they also have access to all the other columns in the employee table. There’s no separate supervisor table, but we can determine an employee who supervises other employee by looking at the reports_to column.

When e1 is joined with e2, e2 represents the employees who supervise the employees in e1. Because of that, you can use e2.first_name and e2.last_name to represent the supervisor’s name, while e1.first_name and e1.last_name instead represents the employees being supervised.

Feel free to tell me if that’s unclear.

1 Like

Oh I see, I had to zoom out a little to view the table. Thanks for the help.

1 Like

No worries. Glad to help.