2

All, I am trying to figure out the syntax for doing joins between subsets of the same table.

  1. I have:

    Employee ( EmpId PK , EmpFirst, EmpLast, EmpMid, DateHired, SSN, DateBirth, Gender, PhoneNum, ReportsTo)

And I want to find , for each employee, the person they report to.

So I am thinking of using:

select empFirst, emplast, empId as Managers inner join
       (select employeeid, empfirstname, emplastname, reportsTo, from Employee) AS Staff
on Managers.employeeid= Staff.reportsTo.

But it seems I need to do something that does not make sense to me, the part between the ** s:


**select managers.employeeid, managers.empfirstname , managers.emplastname , staffmembers.emplastname , staffmembers.reportsTo ** from (select employeeid, empfirstname, emplastname from employee) AS managers inner join (select employeeid , empfirstname, emplastname, reports to from employee) AS Staff on managers.employeeid = staff.reportsTo


Why do we use the part between the ** s? I am trying to see the logic behind the syntax, but I am having trouble getting it. Besides, this inner join of subsets (I assume each subset is itself a table) does not seem to agree with the usual definition of the inner join of tables.

  1. Is it possible to just rename the table as, say Staff, keep the original one and then use a join between staff and employee?
2
  • What database do you use.
    – Marco
    Dec 17, 2015 at 9:34
  • Sorry, MSSQL2014.
    – MSIS
    Dec 17, 2015 at 9:36

1 Answer 1

2

You are trying to do self join which is perfectly fine in this case. You can create another identical table and rename it, but is not necessary. You should write something like this:

    SELECT m.employeeid AS ManId, m.empfirstname AS ManFirstName,     
    m.emplastname AS ManLastName,
    s.employeeid AS StaffId, s.empfirstname AS StaffFirstName,
    s.emplastname AS StaffLastName
    FROM Employee s
    JOIN Employee m
    ON s.ReportsTo = m.employeeid
4
  • 1
    This answer perfectly meets your criteria. But be aware that this will not show the person (CEO?) that does not report to anyone at all. This person will be missing in the list of employees.
    – Magier
    Dec 17, 2015 at 10:57
  • 1
    Correct, if we want to show employees with nulls in ReportsTo then Left Join is needed. Dec 17, 2015 at 11:40
  • Thanks; just to verify: this is a MSSQL2014 join, right, not Oracle or some other version?
    – MSIS
    Dec 18, 2015 at 7:19
  • Yes this script is for sql server. Dec 20, 2015 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.