Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Inner Joins between Subsets of the Same Table.

  1. Dec 17, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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 Staff


    on managers.employeeid = staff.reportsTo


    _____________________________________________________________________



    Why do we use the part between the ** s?

    Thanks.
     
  2. jcsd
  3. Dec 17, 2015 #2

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    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
     
  4. Dec 17, 2015 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

  5. Dec 18, 2015 #4

    jim mcnamara

    User Avatar

    Staff: Mentor

    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
     
  6. Dec 18, 2015 #5

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  7. Dec 21, 2015 #6

    PAllen

    User Avatar
    Science Advisor
    Gold Member

    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.]
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Inner Joins between Subsets of the Same Table.
  1. Writing a Table (Replies: 6)

  2. Matlab and tables (Replies: 0)

  3. Sql join? (Replies: 4)

Loading...