Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

How do I show employee and supervisor name using join?

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?
1 Solution
Shaun KlineLead Software EngineerCommented:
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
batesitAuthor Commented:
Excellent, thanks.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now