You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Get full span of employees under a Manager

4 replies [Last post]
Guest's picture
Guest

Hi, I need to select full span of employees under a Manager, say if X is the manager, and A, B, C report to X, where A is also a manager and D, E, F report to A, Now I need to write a sql which gets manager name as the input, If I enter X as the manager name it should show A, B, C, D, E, F in the output. Please let me know how to achieve this. If you have any Sql pls share it. I need to use this in an SQR.

rahulkhandelw's picture
User offline. Last seen 9 years 28 weeks ago. Offline
Joined: 10/16/2009
Posts: 34
Re: Get full span of employees under a Manager

If your database platform is oracle then you can use start with--connect by clause.

Here is sample SQL
SELECT emplid, supervisor_id
FROM ps_employees
START WITH supervisor_id = '1122456'
CONNECT BY PRIOR emplid = supervisor_id;

Regrads,
Rahul Khandelwal

Lepa's picture
User offline. Last seen 2 years 6 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Get full span of employees under a Manager

here is a blog post that will explain what rahulkhandelw is talking about http://www.compshack.com/sql/start-with-and-connect-by-oracle-sql

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Guest's picture
Guest (not verified)
Re: Get full span of employees under a Manager

Thanks, this worked and its pretty simple to understand!!

Guest's picture
Guest (not verified)
Re: Get full span of employees under a Manager

How to use "connect by prior" to select value from Job table based on effective date or let me know how to give effective date condition in the above mentioned sql