Employees and Their Managers

Mediumsql

Write a self-join query returning id, name (employee), and manager (their manager's name, or NULL if they have none), ordered by employee id.

sql
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  manager_id INTEGER,
  salary INTEGER NOT NULL
);

Sample data:

sql
INSERT INTO departments (id, name) VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Marketing');
INSERT INTO employees (id, name, department_id, manager_id, salary) VALUES
(1, 'Asha', 1, NULL, 90000),
(2, 'Rohan', 1, 1, 70000),
(3, 'Neha', 2, 1, 65000),
(4, 'Vikram', NULL, 1, 50000);

Example 1

Input

(none)

Output

id	name	manager
1	Asha	NULL
2	Rohan	Asha
3	Neha	Asha
4	Vikram	Asha

Join employees to itself with two aliases, one for the employee and one for the manager. Reference: SELECT e.id, e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id ORDER BY e.id;

Employees and Their Managers

Related Problems

Input (stdin)

Output