Search code examples
sqloracle-databasehierarchy

Writing a query returning all members of a hierarchy

I'm having trouble writing a query that seems rather complicated for my level.

The problem is easy: it uses only one table ressources(id_ressource, name, id_manager)

As you may guess, id_manager refers to an other id_ressource. What I want is to get a list of all ressources under a specific manager. But a manager might by the manager of others managers and so on: I want to get all those ressources under him, including managers themselves.

So far the query I have is most simple:

SELECT name FROM ressources WHERE id_manager = :MANAGER_VAR;

I know I might be using some of CONNECT BY, PRIOR, but I have almost no experience with it and would gladly appreciate any help.

Solution

  • The hierarchical query (connect by) looks like this:

    select  name
from    ressources               --  check your spelling?
start   with id_ressource = :manager_var
connect by   id_manager   = prior id_ressource
;

    Here :manager_var is the input variable (bind variable, as in your attempt).