How do I show employee and supervisor name using join?

Posted on 2014-08-07
Last Modified: 2014-08-07
I feel I'm so close, but am missing something obviously. I want to get the name of both the employee and supervisor where the name is stored in one table from a different database and the supervisor lookup table references the employee and supervisor id number.
Emp_M                                                           SupLookup
  SID                                                                    SID
  Emp_name                                                      SupID

SID in SupLookup table references the employee, SupID in SupLookup references the supervisor. SID in Emp_M table references all employees (including supervisors).

This is what I have so far:
select b.SupID, b.status, b.EmpJobNum, a.EMP_NAME
from hpsa.[EMP].emp_m a
inner join TimeCardsV3.dbo.SupLookup b
on a.sid = b.SID

This gives me the name of the employee, but not the supervisor. If I call emp_name again, it just gives me the employee name again. I can't figure out how to connect the SupID to the SID without breaking the employee part. What am I missing?
Question by:batesit
    LVL 25

    Accepted Solution

    You need to add a second join to the emp_m table:

    from hpsa.[EMP].emp_m a
     inner join TimeCardsV3.dbo.SupLookup b on a.sid = b.SID
    inner join hpsa.[EMP].emp_m c on b.supID = c.sid

    The supervisor name is then c.Emp_name
    LVL 1

    Author Closing Comment

    Excellent, thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now