Noble Six Taniguchi
Noble Six Taniguchi

Reputation: 189

SQL- Using a value in one tuple to get a value from another one

I have a table called employee which contains the following columns -

Fname, Lname, ssn (primary key), salary, supervisor ssn, departmentname.

Now for each employee in the table belonging to the department 'Research', I want to output his name, salary and the NAME of his supervisor. What I currently have is this.

SELECT fname, salary, superssn from employee where departmentname='Research';

Now this gives me only the ssn of the employee's supervisor, and not the supervisor's name. I know that I have to compare the superssn of an employee with another employee who has the same ssn and get that other employee's name since he's he supervisor, but I'm not sure how to implement this in the same command.

Upvotes: 2

Views: 445

Answers (2)

Fabian
Fabian

Reputation: 2972

You need a self join:

SELECT researcher.fname, researcher.salary, supervisor.fname 
FROM employee researcher JOIN employee supervisor ON researcher.superssn = supervisor.ssn
WHERE researcher.departmentname='Research'; 

This gives you all related pairs from the cross product of employee x employee that match on the supervisor / researcher ssn.

When you want to include researchers without a supervisor, too, then you need a LEFT JOIN.

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You need a join back to the emplyee table (and you need an alias to reference that table), like this:

SELECT employee.fname, employee.salary, supervisorlist.fname as supervisor 
from employee 
left join employee as supervisorlist on supervisorlist.ssn = employee.supervisorssn
where employee.departmentname='Research';

Upvotes: 2

Related Questions