Puzzle of the Week #21:
Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:
- Use Single SELECT statement only
- Use mgr column to identify employee’s immediate manager
- The query should work in Oracle 11g.
- A preferred solution should use only a single instance of emp table.
NAME1 NAME2 NAME3 ---------- ---------- ------ SMITH FORD JONES ALLEN BLAKE KING WARD BLAKE KING JONES KING MARTIN BLAKE KING BLAKE KING CLARK KING SCOTT JONES KING KING TURNER BLAKE KING ADAMS SCOTT JONES JAMES BLAKE KING FORD JONES KING MILLER CLARK KING
To submit your answer (one or more!) please start following this blog and add a comment to this post.
A correct answer (and workarounds!) will be published here in a week.
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.