Practise · The staff chart loses its top row

Opening your saved work…

The reading

A table meets itself

Nothing says the two sides of a join must be different tables. The staff table has a supervisor_id that points at another row of staff. Give the table two aliases and it becomes two copies: e for the employee, m for the manager that e.supervisor_id points at. The aliases name roles, not two tables stored twice.

The bridge is ON m.id = e.supervisor_id. Write ON m.id = e.id instead and every person becomes their own supervisor, fourteen rows of nonsense that run without a complaint.

SELECT e.id AS employee_id, e.first_name AS employee, m.first_name AS supervisor
FROM staff e
LEFT JOIN staff m ON m.id = e.supervisor_id
ORDER BY e.id;

Fourteen rows. Marit, at the top, has no supervisor and is still on the chart. Open in the playground

One row of staff has a NULL supervisor_id: the director, at the top of the chart. An INNER JOIN drops her, because NULL matches nothing. If the chart must show every employee, the employee copy goes on the left of a LEFT JOIN and the director keeps her row with a NULL where a supervisor's name would be.

Rule of thumb. In a self-join the aliases are roles. Pair the pointing column with the key it points at, and keep the top row with a LEFT JOIN.

Going deeper (optional, not on the exam)

The same trick answers questions inside one table of facts: who ran the marathon in both 2024 and 2025, and did they get faster? Pair a results row with another results row of the same runner, one alias per year.

The staff chart loses its top row · one table, two roles · level 6 of 6

A supervisor at another branch

Level 6: Transfer it · Exercise 1 of 1

Same idea as The staff chart loses its top row, new data.

Which staff members report to a supervisor who works at a different branch? Return first_name, last_name, home_branch (the employee's branch name) and supervisor_branch, ordered by employee id. You will need the branches table twice.

Finish line: 4 rows · columns first_name, last_name, home_branch, supervisor_branch · in the order the question gives

Data: Twin Ports Tool Library.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.