Re: AN SQL question

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 25 Feb 2002 17:53:07 +0300
Message-ID: <a5div1$11a$1_at_babylon.agtel.net>


Use START WITH ... CONNECT BY ... PRIOR ... to create a hierarchical query (RTM on this).

Something like this will do:

select t.*, level from tbl_users t start with supervisorID = 73676  connect by supervisorID = prior employeeID;

LEVEL pseudo-column will indicate where at the hierarchy particular record is. Top level is 1, next is 2, etc. Rows on each level will be in no particular order, but each row will have its child rows (if any) following immediately.

hth.

--
Vladimir Zakharychev (bob_at_dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Kevin" <kevkim_at_nb.sympatico.ca> wrote in message
news:6Mqe8.7722$Ek1.936882_at_news-nb00s0.nbnet.nb.ca...

> Hey all,
>
> I have a table called tbl_users.
> The columns in the table are: employeeID, name, dept, supervisorID
> [employeeID, dept and supervisorID are numbers]
> -supervisorID is linked to employeeID; supervisorID is actually an
> employeeID in the same table.
> -ex: the supervisorID in Kevin's record is 23454, which is Linda's
> employeeID, ie Linda is Kevin's supervisor, yet Linda is an employee herself
> who also has a supervisor.
>
> I need an SQL query where all employees would be displayed that report to a
> certain suprevisor, AND if any of those employees are supervisors, the query
> should also display any of the employees under them, and so on until it has
> cascaded to the bottom, so to speak.
>
> Example:
> If Tom enters his supervisorID (73676) as the parameter to the query (I'm
> using Cognos Impromptu, for those curious), he would see all employees that
> report to him, including Linda. He would also see all the employees that
> report to Linda, including Kevin. He would also see all the employees that
> report to Kevin, etc.
>
> I have come up with something like this, but it only gives me a couple of
> levels:
>
> select * from tbl_users where supervisorID in (select employeeID from
> tbl_users where supervisorID = 73676)
>
> The dilemma is that I can't possibly know at which level the supervisor is
> at. He may have 2 levels that report to him or 10.
>
> Is there another way to build this query to get all the employees under a
> supervisor cascaded to the very bottom?
>
> Thanks!
>
> Kevin
>
>
>
>
Received on Mon Feb 25 2002 - 15:53:07 CET

Original text of this message