/* Performing a self join */
/* a self join is joining a table to itself */
/* actually it is joining 2 versions of the same table to one another */


/* in a hierarchical table, I can unravel the hierarchy */

SELECT mgr.fname "Mgr First" , mgr.lname "Mgr Last", emp.fname "Staff First", emp.lname "Staff Last"
FROM   hierarchy mgr JOIN hierarchy emp
  ON   mgr.employee_id = emp.manager_id;



/* left join */

SELECT mgr.fname "Mgr First" , mgr.lname "Mgr Last", emp.fname "Staff First", emp.lname "Staff Last"
FROM   hierarchy mgr LEFT JOIN hierarchy emp
  ON   mgr.employee_id = emp.manager_id;



/* right join */

SELECT mgr.fname "Mgr First" , mgr.lname "Mgr Last", emp.fname "Staff First", emp.lname "Staff Last"
FROM   hierarchy mgr RIGHT JOIN hierarchy emp
  ON   mgr.employee_id = emp.manager_id
ORDER BY mgr.lname;
  
  

/* The above was looking at the query from a manager point of view */
/* Below similar queries from an employee point of view */

SELECT emp.fname "Staff First" , emp.lname "Staff Last", mgr.fname "Mgr First", mgr.lname "Mgr Last"
FROM   hierarchy emp LEFT JOIN hierarchy mgr
  ON   emp.manager_id = mgr.employee_id
ORDER BY emp.lname;