All, I am trying to figure out the syntax for doing joins between subsets of the same table.
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.
- Is it possible to just rename the table as, say Staff, keep the original one and then use a join between staff and employee?