Select the names of employees who are not administrators

Question

Select the names of employees who are not administrators

Navigation

#1 by (3 votes)

-1

I am trying to perform a query based on the hierarchy of employees in an organization.

An employee is an administrator if any other employee has their managerId set to the first employee id. An employee who is an administrator may or may not be an administrator as well.

TABLE employees
  id INTEGER NOT NULL PRIMARY KEY
  managerId INTEGER REFERENCES employees(id)
  name VARCHAR(30) NOT NULL

-- Example case create statement:
CREATE TABLE employees (
  id INTEGER NOT NULL PRIMARY KEY,
  managerId INTEGER REFERENCES employees(id), 
  name VARCHAR(30) NOT NULL
);

INSERT INTO employees(id, managerId, name) VALUES(1, NULL, 'John');
INSERT INTO employees(id, managerId, name) VALUES(2, 1, 'Mike');

-- Salida esperada
-- name
-- ----
-- Mike

-- Explicación:
-- En este ejemplo.
-- John es el administrador de Mike. Mike no administra a nadie.
-- Mike es el único empleado que no administra a nadie.

How can I perform the query? I have tried to make several queries but none of them gives me the desired result because I can not capture the idea very well in my head.

The exercise is taken from here .

sql

asked by Hoose 27.11.2017 в 17:10

source

1 answer

Problem Database How can I remove these white bits from my website?

score 3 · Accepted Answer

It seems that you should simply find the employee ids that do not exist as managerId in the same table. So this query seems to be enough:

SELECT name
FROM employees e
WHERE NOT EXISTS(SELECT 1 FROM employees
                 WHERE managerId = e.Id);