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?
  • What database do you use. – Marco Dec 17 '15 at 9:34
  • Sorry, MSSQL2014. – MSIS Dec 17 '15 at 9:36
1

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
  • 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 '15 at 10:57
  • 1
    Correct, if we want to show employees with nulls in ReportsTo then Left Join is needed. – Michael Cherevko Dec 17 '15 at 11:40
  • Thanks; just to verify: this is a MSSQL2014 join, right, not Oracle or some other version? – MSIS Dec 18 '15 at 7:19
  • Yes this script is for sql server. – Michael Cherevko Dec 20 '15 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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