>_ IzzyLab

Hands-on task

This task is step 13 of the course “SQL with SQLite”

SQLite: join a table to itself

medium~7 min

The database /root/org.db has a table employees(id INTEGER, name TEXT, manager_id INTEGER), where manager_id references the id of a manager in the same table (for the top boss it is NULL). Write a query with a self-join (joining the table to itself) that, for every employee who has a manager, outputs a pair employee|manager, and write the result to /root/pairs.txt.

What you'll practise:
  • self-join
  • join
  • table aliases
  • hierarchy
  • sqlite3

Topic breakdown

A self-join is an ordinary JOIN where a table is joined to itself; to distinguish the "two copies", each is given its own alias, and one copy plays the role of the employee, the other of the manager. The link condition is set in ON, joining the reference column of one copy to the key of the other. INNER JOIN automatically drops rows without a match (for example, a boss with NULL in the reference), whereas LEFT JOIN would keep them. This way a single table models hierarchical relationships like "subordinate → manager".

What's checked

  1. subordinates of the top manager found
  2. second-level management chain is correct
  3. the employee without a manager is not in the result

What it looks like