How to write self join query

  • 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

  • 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.

  • 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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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