CODEWITHSUNDEEP
sql
student
student

Reputation: 78

Find all direct subordinates of a particular employee in SQL

I have a table of employees and their bosses:

CREATE TABLE IF NOT EXISTS Personnel (

emp CHAR(10) NOT NULL PRIMARY KEY,

boss CHAR(10) DEFAULT NULL REFERENCES Personnel(emp),

salary DECIMAL(6,2) NOT NULL DEFAULT 100.00);

enter image description here I need to find all direct subordinates of a particular employee. My code shows only one employee for each boss:

SELECT (SELECT E2.emp FROM Personnel E2 WHERE E1.emp = E2.boss), E1.emp FROM Personnel E1;

Problem: is to print all the employees of each boss in one row!
Here is the code to construct the database:

INSERT INTO Personnel VALUES('Ivanov', Null, 1000);

INSERT INTO Personnel VALUES('Petrov', 'Ivanov', 900);

INSERT INTO Personnel VALUES('Sidorov', 'Ivanov', 850);

INSERT INTO Personnel VALUES('Smirnov', 'Petrov', 840);

INSERT INTO Personnel VALUES('Fedorov', 'Petrov', 800);

INSERT INTO Personnel VALUES('Andreev', 'Sidorov', 700);

INSERT INTO Personnel VALUES('Alekseev', 'Sidorov', 700);

INSERT INTO Personnel VALUES('Borisov', 'Sidorov', 740);

Upvotes: 1

Views: 190

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

"Direct subordinate" means that the boss is the person in question. Hence a simple where clause should suffice:

select p.*
from personnel p
where p.boss = ?;

The ? is a placeholder for the boss's name.

Upvotes: 1

Related Questions