Employees with Manager Titles — Self-Joins in SQL

The problem

Helix Systems' HR team is expanding the employee directory to include management context.

Write a query to return each employee's name alongside their manager's name and the manager's job title.

Assumptions:

The employees table contains every active and former employee at Helix Systems.
Both the employee role and the manager role come from the same employees table — the manager-side alias also exposes the title column.
Employees with no manager (manager_id is NULL) are excluded.

Output:

One row per employee with a manager, with columns employee_name, manager_name, and manager_title.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  e.name AS employee_name,
  m.name AS manager_name,
  m.title AS manager_title
FROM
  employees e
  JOIN employees m ON e.manager_id = m.id

The shape

Each alias is a fully independent reference to employees, so the manager role can expose both name and title even though they come from the same physical table as the employee's row.

Clause by clause

SELECT e.name AS employee_name, m.name AS manager_name, m.title AS manager_title pulls three columns from two aliased instances. e.name comes from the employee role; m.name and m.title both come from the manager role and resolve against the manager's row, not the employee's.
FROM employees e reads the table in the employee role.
JOIN employees m ON e.manager_id = m.id reads the same table again in the manager role. Every column on m (name, title, manager_id, department_id, anything else) is available on this side of the join and resolves to the manager's row.

The trap

A self-join is not a copy of the table with a few columns exposed. Both aliases see the full row. The reader who thinks m is somehow restricted to id and name because that's what the join condition mentions will be surprised by m.title working without any extra setup. Once an alias is declared, every column on that table is reachable through it.

You practiced selecting two different columns from the same physical table by reaching into both aliased instances. The recurring rule: each alias is a fully independent reference — e.name and m.title come from the same table but two different rows.

Return each employee's name alongside their manager's name and the manager's job title

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.