Reputation: 189
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
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
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