sql query for retreive data from same table

Posted by Shamseena under C# on 12/20/2011 | Points: 10 | Views : 1106 | Status : [Member] | Replies : 4
hi all,

I am working with the web application with the sql server database.In the database i have one table named 'Employee'.It consists of empid,empcode,empname and managerid. managerid is the same person from employee.The data of this table will be like this

empid empcode empname managerid
1 001 Raj 4
2 002 Rani 1
3 003 Kamal 4
4 004 Lakshmi 2
5 005 Karan 2
6 006 Das 1


How can i get the manager name instead of the managerid. Both empid and managerid are from same table.



The expected output is

empid empcode empname managerid managername
1 001 Raj 4 Lakshmi
2 002 Rani 1 Raj
3 003 Kamal 4 Lakshmi
4 004 Lakshmi 2 Rani
5 005 Karan 2 Rani
6 006 Das 1 Raj

expecting a good result.


thanks in advance
shamsi




Responses

Posted by: Sksingh on: 12/20/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Try below query

select e1.empid,e1.empcode,e1.empname,

e2.managerid,e2.empname as managername
from Employee e1 left outer join Employee e2
on e1.empid = e2.managerid
order by e1.empid


Regards,
Sunil

Shamseena, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Irshadvaza on: 12/20/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Shamsi,

You can use self join for getting such type of result.

e.g

SELECT Emp.empid,Emp.empcode,Emp.empname,Mgr.managerid,Mgr.empname AS ManagerName
FROM Employee Emp LEFT OUTER JOIN Employee Mgr
ON Emp.empid = Mgr.managerid
ORDER BY Emp.empid

By using this query,Employee table join with Employee table(self) and get result,if try to run query and let me know if you have any query.

Thanks & Regards,
Irshad Vaza

Shamseena, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 12/20/2011 [Member] Silver | Points: 25

Up
0
Down
create table emp(empid int, empcode varchar(5),empname varchar(20),managerid int)

insert into emp values(1, '001', 'Raj', 4 )
insert into emp values(2, '002', 'Rani', 1 )
insert into emp values(3, '003', 'Kamal', 4 )
insert into emp values(4, '004', 'Lakshmi', 2 )
insert into emp values(5, '005', 'Karan', 2 )
insert into emp values(6, '006', 'Das',1 )



select e1.empcode,e1.empname,e1.managerid
,Manager=(select e2.empname from emp e2 where e2.empid =e1.managerid )
from emp e1
------------------------


Thanks,
Sanjay

Shamseena, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 12/20/2011 [Member] Silver | Points: 25

Up
0
Down
create table emp(empid int, empcode varchar(5),empname varchar(20),managerid int)

insert into emp values(1, '001', 'Raj', 4 )
insert into emp values(2, '002', 'Rani', 1 )
insert into emp values(3, '003', 'Kamal', 4 )
insert into emp values(4, '004', 'Lakshmi', 2 )
insert into emp values(5, '005', 'Karan', 2 )
insert into emp values(6, '006', 'Das',1 )



select e1.empcode,e1.empname,e1.managerid
,Manager=(select e2.empname from emp e2 where e2.empid =e1.managerid )
from emp e1

select e1.empid,e1.empcode,e1.empname,

e2.managerid,e2.empname as managername

from emp e1 Left outer join emp e2

on e1.managerid =e2.empid

Thanks,
Sanjay

Shamseena, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response