Q:

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.  

A:

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';

