Inner Joins between Subsets of the Same Table

Posted on

Question :

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?

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *