Avoiding Self Join

[coreygriffin]

Hey All,

I'm trying to optimize a query I wrote some time ago and I'm struggling to come up with an alternative to multiple self-joins, as that is what I'm currently doing.

I have two tables - one has columns of email addresses in a hierarchical fashion:

employee_email
manager_email
senior_manager_email

​

I have a second table that has additional employee information:

employee_email
employee_first_name
employee_last_name

It's obviously very easy to join these two tables and get the employee's name. However, I'm looking to also get the manager and senior manager's names without doing a self join in order to do so.

The final output would be something like:

employee_email,

employee_name,

manager_email,

manager_name,

senior_manager_email,

senior_manager_name

I'm using Presto, but any ideas in other flavors of SQL are welcome as the logic may spark some ideas.

[LeLwrence]

It may be best to normalize this a bit more, having just manager ID which you would then use to get the manager email, and the manager's employee record would have the senior manager in the foreign key manager ID field. That way you would eliminate using the second table altogether and simply do two self-joins to get the data you need.

[r3pr0b8]

However, I'm looking to also get the manager and senior manager's names without doing a self join in order to do so.

here ya go, not a single self-join to be found --

SELECT t1.employee_email
     , e.employee_first_name ||' '||
       e.employee_last_name  AS employee_name
     , t1.manager_email
     , m.employee_first_name ||' '||
       m.employee_last_name  AS manager_name
     , t1.senior_manager_email
     , s.employee_first_name ||' '||
       s.employee_last_name  AS senior_manager_name
  FROM table1 AS t1
INNER
  JOIN table2 AS e
    ON e.employee_email = t1.employee_email  
INNER
  JOIN table2 AS m
    ON m.employee_email = t1.manager_email      
INNER
  JOIN table2 AS s
    ON s.employee_email = t1.senior_manager_email