Query that selects the names of all employees and the names of their bosses

Question

I need to write a query that selects the names of all employees and the names of their bosses. If there is no boss for an employee, return NULL. Bosses are consider to be employees as well.

Table:

CREATE TABLE employees (
  id INTEGER NOT NULL PRIMARY KEY,
  bossId INTEGER,
  name VARCHAR(20) NOT NULL,
  FOREIGN KEY (bossId) REFERENCES employees(id)
  );
  
  INSERT INTO employees(id, bossId, name) VALUES(1, NULL, 'Fred');
  INSERT INTO employees(id, bossId, name) VALUES(2, 1, 'Jason');
  INSERT INTO employees(id, bossId, name) VALUES(3, 1, 'Mike');

Expected result:

name	boss
Fred
Jason	Fred
Mike	Fred

This looks like an assignment. Have you tried writing any SQL code yet? — Tim Biegeleisen, May 27 '22 at 08:38
check this https://stackoverflow.com/questions/19914472/cte-to-get-all-children-descendants-of-a-parent — RegBes, May 27 '22 at 08:39

score 1 · Accepted Answer · answered May 27 '22 at 08:59

1

You need left join, if the employee doesn't have a boss then show null in output

select emp.name,empBoss.name
from employees  emp 
left join employees empBoss
on emp.bossId = empBoss.id

answered May 27 '22 at 08:59

thisisnabi

1,045
9
21

1

I thought Joins use only with two or more tables… – peterSK13 May 27 '22 at 09:17
Oh, yes you can use join for one table. It's Join a table to itself and you need this for the same query like "Companies that are fellow citizens" over the company table. – thisisnabi May 27 '22 at 13:20
Thanks again, now I finaly understand. – peterSK13 May 28 '22 at 16:05

Query that selects the names of all employees and the names of their bosses

1 Answers1