CODEWITHSUNDEEP
sql-servert-sql
Bhawna Jain
Bhawna Jain

Reputation: 759

select command for foreign key within same table

Hi guys i have this given table..in which i want to have a select query to replace managerid with the name of the employee with which it is associated.

For example: i want output as

1 | andy | sw |andy

2 | brij | sw |andy

3 | toby | sw |brij

enter image description here

Is it possible to do this within same table .

Upvotes: 2

Views: 1868

Answers (7)

Shoukat Mirza
Shoukat Mirza

Reputation: 828

    select e2.EMPID,e2.EMPNAME, COALESCE(e1.EMPNAME,e2.EMPNAME,e1.EMPNAME) as Mannager
from `employee` e1 right join `employee` e2
on e1.empid=e2.managerid

Output :.enter image description here

Upvotes: 0

Shoukat Mirza
Shoukat Mirza

Reputation: 828

You can use SELF_JOIN in the case when you want to fetch records from same table.

For example:

Table Name: employee

Fields : EMPID , EMPNAME , MANAGERID

Now if you want to get the details of Empolyees who are in Manager Position for that we need to write query like this:

    select e2.EMPID,e2.EMPNAME, COALESCE(e1.EMPNAME,e2.EMPNAME,e1.EMPNAME) as Mannager
from `employee` e1 right join `employee` e2
on e1.empid=e2.managerid

Upvotes: 0

Karnik Shukla
Karnik Shukla

Reputation: 1

DECLARE @Emp table (EmpID INT,EmpName varchar(100),Designation varchar(10),MgrID INT)

INSERT INTO @Emp(EmpID,EMpName,Designation,MgrID)
SELECT *
FROM ( VALUES(1,'andy','sw',NULL),
             (2,'brij','sw',1),
             (3,'toby','sw',2)
)v (EmpID,EMpName,Designation,MgrID)

SELECt e.EmpID,e.EMpName,e.Designation,ISNULL(e1.EmpName,e.EmpName) AS Mgr 
FROM @Emp e
LEFT JOIN @Emp e1 ON e.MgrID = e1.EmpID

Upvotes: 0

Mahesh
Mahesh

Reputation: 361

Your query should be 

SELECT
  a.EMPID,
  a.EMPNAME,
  a.DESIGNATION,
  (Select b.EMPNAME from Employee as b where a.MANAGERID = b.EMPID) as    
  MANAGER
  FROM
  Employee as a

Upvotes: 1

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

Also try this way:

 SELECT EMPID,EMPNAME,DESIGNATION, case when 'MANAGERID' <> EMPNAME then EMPNAME else null end as MANAGERID FROM YOURTABLE

Upvotes: 1

Bhawna Jain
Bhawna Jain

Reputation: 759

Apart from @Shakeer's answer,i found one more alternative.This one is without left join..but a self join.

SELECT e.empid, e.empname, m.empname  "Manager" FROM test_Emp e, test_Emp m WHERE e.managerid=m.empid;

Upvotes: 5

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Just do self Join to know the Manager name.

SELECT E1.EMPNAME, E1.DESIGNATION, E2.EMPNAME AS MANAGER_NME
FROM EMPLOYEE E1
LEFT JOIN EMPLOYEE E2 ON E1.MANAGER_ID = E2.EMPID

Upvotes: 1

Related Questions