开发者

Deriving a column's data from a matching column in SQL

So I have a table that has, employee number, employee name, supervisor number.

I want to run a query that will retrieve employee name, employee number, supervisor name and supervisor number. Only one employee doesn't have a supervisor meaning it will have to display nulls. Ho开发者_如何学Gow would I do this? I'm using Oracle SQL Plus. My attempts haven't worked at all! Any help would be much appreciated.

SELECT ename Employee, empno Emp#, super Manager#
FROM emp;

That gets me three of the columns but to be honest I don't even know where to start to get the supervisors names.

It's for university, but I'm studying for a test it's not for an assignment so no cheating happening here :).


The following should work, and give you nulls if the employee has no 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


Assuming that SupervisorNumber is a foreign key relationship back to the Employee table (where it's the EmployeeNumber of the supervisor's record), then you need to use an outer join.

What you need in this case is a left join:

select
    e.EmployeeName,
    e.EmployeeNumber,
    s.EmployeeName as SupervisorName

from Employee e

left join Employee s on s.EmployeeNumber = e.SupervisorNumber
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜