CODEWITHSUNDEEP
mysql
Almis
Almis

Reputation: 3819

Get self reference column

Let's say we have employee table

+----+------+--------------+--------------+
| Id | Name | SupervisorId | IsSupervisor |
+----+------+--------------+--------------+

and supervisor table

+----+---------+
| Id | Section |
+----+---------+

And what I want is to make this table

+------------+--------------+--------------+----------------+
| EmployeeID | EmployeeName | SupervisorId | SupervisorName |
+------------+--------------+--------------+----------------+

Supervisor should be empty if not exist

My query ended up with something like this line with question mark is the one hat need to be replaced

Select
  employees.Id as EmployeeID,
  employees.Name as EmployeeName,
  supervisors.Id as SupervisorId,
  supervisors.Name as SupervisorName # ???
From
  employees Left Join
  supervisors
    On employees.SupervisorId = supervisors.Id

Upvotes: 0

Views: 34

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

try something like this, getting supervisor's name out of Employees(E2) table.

Select
    E.Id as EmployeeID,
    E.Name as EmployeeName,
    S.Id as SupervisorId,
    E2.Name as SupervisorName
From
    Employees E 
    Left Join
    Supervisors S On E.SupervisorId = S.Id
    Left Join
    Employees E2 On E2.Id = S.Id

Upvotes: 1

Related Questions