sql query for retreive data from same table

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

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

0	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

Thanks,
Sanjay

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

Latest Posts