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

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

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

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