So I've got a table which has, worker number, worker title, supervisor number.
I wish to operate a query which will retrieve worker title, worker number, supervisor title and supervisor number. Just one worker does not possess a supervisor meaning it'll have to show nulls. Wouldso would I actually do this? I am using Oracle SQL Plus. My attempts haven't labored whatsoever! Any help could be much appreciated.
SELECT ename Employee, empno Emp#, super Manager# FROM emp;
That will get me three from the posts but to tell the truth I do not know how to start to obtain the administrators names.
It's for college, but I am studying for any test it isn't to have an assignment so no cheating happening here :).
SupervisorNumber is really a foreign key relationship to the
Employee table (where it is the
EmployeeNumber from the supervisor's record), then you should utilize an outer join.
The thing you need within this situation is really a
select e.EmployeeName, e.EmployeeNumber, s.EmployeeName as SupervisorName from Employee e left join Employee s on s.EmployeeNumber = e.SupervisorNumber
The next should work, and provide you with nulls when the worker doesn't have supervisor:
SELECT empGrunt.ename Employee , empGrunt.empno EmpNum , empSuper.ename SupervisorName , empSuper.empno SupervisorName FROM emp empGrunt LEFT OUTER JOIN emp empSuper ON empGrunt.super = empSuper.empno