Inner Joins between Subsets of the Same Table.

[WWGD]

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

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 Staffon managers.employeeid = staff.reportsTo_____________________________________________________________________
Why do we use the part between the ** s?

Thanks.

 

[Samy_A]

Seems complicated.

This should work:

select e.empFirst,e.empLast, m.empFirst as managerFirst,m.empLast as managerLast
from employee as e join employee as m on m.empId=e.reportsTo

In real life I wouldn't even use the Join syntax, but use:

select e.empFirst,e.empLast, m.empFirst as managerFirst,m.empLast as managerLast
from employee as e, employee as m where m.empId=e.reportsTo

These two queries give the same result. The result won't include the top people who report to no one.
If you want these too in the query, use a left join:

select e.empFirst,e.empLast, m.empFirst as managerFirst,m.empLast as managerLast
from employee as e left join employee as m on m.empId=e.reportsTo

 

[WWGD]

Thank you, I think I found a good resource. In case anyone is interested:

http://www.w3resource.com/sql/joins/perform-a-self-join.php

 

[jim mcnamara]

Samy's example is the best choice given your exmple. Mor generally, for problems like this, there is the SQL 'start with ... connect by' syntax - it can build a tree from a single table.

http://philip.greenspun.com/sql/trees

 

[WWGD]

Thanks, both. Is there a standard format for queries requiring fields from 3 or more tables? I need to find OrderNum, CustomerName, Quoted price, which are on tables Orders, Customer and OrderLine respectively.
Orders and Customer are related to each other, so are Order and OrderLine. Should I just first Join Orders with Customer and then join the result to OrderLine (together with the grouping by CustomerName)?

Specifically, I was thinking of doing this:

Select OrderNum, CustomerName, QuotedPrice from
Orders inner join Customer on Orders.CustomerNum = Customer. CustomerNum
inner join Orderline on Orders. OrderNum = Orderline.OrderNum
group by CustomerNum.

 

[PAllen]

Samy's example is the best choice given your exmple. Mor generally, for problems like this, there is the SQL 'start with ... connect by' syntax - it can build a tree from a single table.

http://philip.greenspun.com/sql/trees

Worth noting that CONNECT BY is is not standard SQL (either official or industry de-facto). It is supported by a couple of the big players, but not by the other ones. There is standard syntax for such operations, which is supported by a larger set of vendors:

Here is an example (from one vendor, but using standard syntax for recursive queries):

https://msdn.microsoft.com/en-us/library/ms186243.aspx

[Note, for example, that Oracle supports both CONNECT BY, which they originated as proprietary syntax [I believe before any standard syntax existed], as well as supporting the standard syntax described above.]