Extract a sub-tree from a hierarchy tree based on a leaf in Oracle

Question

I have a table users representing a hierarchical tree like this:

Column	Type	Comment
user_id	integer	sequence
user_type	integer	1 for group of users 2 for normal user
group_id	integer	Reference to a user in the same table with user_type = 1
user_name	varchar(xxx)

The group_id column references another user_id so that groups and users are stored in the same table.

The master group_id is 0.

Like this:

user_id	user_type	group_id	user_name
0	1	null	'All users'
5	2	0	'USER1'
6	2	0	'USER2'
11	1	0	'SUBGROUP1'
12	1	11	'SUBGROUP2'
13	2	12	'USER3'
20	1	0	'SUBGROUP3'
21	2	20	'USER4'

Notice that:

There can be gaps in user_id.
A group can contain nothing or any number of groups or users.

I have already managed to retrieve the full tree, properly indented and sorted, by using the connect by oracle statement.

This is not my question here.

My question is:

Given a user_id to a query, how to browse the tree up to the master group 'All Users' and output as a result the full path from the leaf to the master group ?

Example 1: I run the query for USER1, i want the following output:

All Users
- USER1

Example 2: I run the same query for USER3, i want the following output:

All Users
- SUBGROUP1
-- SUBGROUP2
--- USER3

I hope someone could help me on this.

For information i post the query to retrieve the full tree, for you to see the use of connect by and start with. I'm sure this query is close to the one i want, but my tries never produce the result i want.

select 
  lpad('-', (level - 1) * 2, ' ') || u.user_name as padded_name, 
  u.userid, 
  u.user_group, 
  u.user_type,
  level
from users u
connect by prior u.user_id = u.group_id
start with u.user_id = 0
order siblings by upper(u.user_name);

score 2 · Accepted Answer · answered Feb 16 '21 at 17:42

You could use connect by to walk in the opposite direction. Then the level will of course be opposite too. So to get the results in the right order and indentation, chain another query based on these results that will use row_number() to determine the indentation:

with base as (
  select 
    u.user_name,
    u.user_id, 
    u.group_id, 
    u.user_type,
    level as lvl
  from users u
  connect by prior u.group_id = u.user_id
  start with u.user_id = 13
)
select 
  lpad('-', (row_number() over (order by lvl desc) - 1) * 2, ' ') || base.user_name
    as padded_name,
  user_id,
  group_id,
  user_type
from base
order by lvl desc;

Extract a sub-tree from a hierarchy tree based on a leaf in Oracle

1 Answers1