Lesson 20 of 30

A table joined to itself

Today at the studio

Every new hire at Studio Mochi has a mentor, and the mentor is also on the staff list. Nagi wants a "who mentors whom" chart. "It is the same table twice," you say. "Yes," says Nagi. "Welcome to Kou's favourite problem."

The idea

The mentor_id column of staff points to another row of staff. To pair them, join the table to itself and give each copy its own name with AS: staff AS worker and staff AS boss.

The aliases tell you which copy a column comes from, so worker.name and boss.name are different columns. Use a LEFT JOIN when some rows have no partner, like the people with no mentor.

Example

SELECT worker.name, boss.role
FROM staff AS worker
JOIN staff AS boss ON worker.mentor_id = boss.id

Your turn

0 of 4 questions

Question 1 of 4

Show each person with the name of their mentor. Only people who have a mentor. Show the person's name, then the mentor's name.