December 9, 2009 at 1:36 pm
Dear All,
Below given is the emp table i want to self join.
How to findout empno.'s manager name
employee table
empno. empfname emplname managerempno
12345Sumanta Panda34512
12346Siddhart Bisoi34513
12347Pradhan Namita34515
12348Sumanta Panda34512
34512Sagar Panda34517
34517Swapnil sarrrry34517
Please help.
Thanks in advance.
Regards,
Sumanta
December 9, 2009 at 2:15 pm
A few hints. The general work for a self join is
select a.x, b.y
from mytable a
inner join mytable b
on a.pk = b.pk
try something like that.
December 9, 2009 at 2:21 pm
CREATE TABLE #employee
(empno INT,empfname VARCHAR(20),emplname VARCHAR(20),managerempno INT)
INSERT INTO #employee
--empno. empfname emplname managerempno
SELECT 12345, 'Sumanta', 'Panda', 34512 UNION ALL
SELECT 12346, 'Siddhart', 'Bisoi', 34513 UNION ALL
SELECT 12347, 'Pradhan', 'Namita', 34515 UNION ALL
SELECT 12348, 'Sumanta', 'Panda', 34512 UNION ALL
SELECT 34512, 'Sagar', 'Panda', 34517 UNION ALL
SELECT 34517, 'Swapnil', 'sarrrry', 0 --34517
SELECT Ax.empno,Ax.empfname,Ax.emplname, B.empfname AS 'mgr fname',B.emplname AS 'Mgr lname'
FROM #employee AS Ax
JOIN #employee AS B
ON Ax.managerempno = B.empno
--clean up
--DROP TABLE #employee
Note that your original data had 34517 Swapnil sarry being his / her own manager - so I edited same figuring Swapnil sarry had to report to someone, someone other than themselves.
Now what I have shown is the code for a select statement contained in SQL help file (Books On Line) subject "Parts of a SELECT Statement". Copied and pasted here from BOL and then table and field names changed to match yours.
Sorry Steve posted this without updatting the forum page and seeing your comment.
December 9, 2009 at 10:56 pm
sk.panda (12/9/2009)
Dear All,Below given is the emp table i want to self join.
How to findout empno.'s manager name
employee table
empno. empfname emplname managerempno
12345Sumanta Panda34512
12346Siddhart Bisoi34513
12347Pradhan Namita34515
12348Sumanta Panda34512
34512Sagar Panda34517
34517Swapnil sarrrry34517
Please help.
Thanks in advance.
Regards,
Sumanta
Hi Sumanta,
For future posts, please see the article at the first link in my signature below. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2009 at 1:41 pm
bitbucket-25253 (12/9/2009)
Note that your original data had 34517 Swapnil sarry being his / her own manager - so I edited same figuring Swapnil sarry had to report to someone, someone other than themselves.
Either that, or Swipnil has the greatest job at his company.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply