CODEWITHSUNDEEP
sqldatabaseoracle-database
Rachel
Rachel

Reputation: 35

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. How 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 :).

Upvotes: 1

Views: 116

Answers (2)

Adam Robinson
Adam Robinson

Reputation: 185693

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

Upvotes: 1

Beep beep
Beep beep

Reputation: 19171

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

Upvotes: 1

Related Questions