0

I'm using oracle database with hibernate. I have a EMPLOYEE table, with a self link back to itself on manager. Something like this:

create table employee(
id number(14) not null,
username varchar(100) not null unique,
manager number(14),

constraint employee_pk primary key (id),
constraint employee_manager_fk foreign key (manager) references employee (id)
);

Currently, I can select a employee and its direct child with:

select e2.*
from employee e2,
(select e.* from employee e where e.username='abc') e1 
where e2.manager = e1.id

union 

select e.* from employee e where e.username='abc'

I want to run a query where when given one employee username I can selected all it's child employees down to the n-th level. How do I do it in a SQL statement?

JackDev
  • 11,003
  • 12
  • 51
  • 68
  • possible duplicate of [Sql server CTE and recursion example](http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) – epoch Jul 08 '14 at 06:07
  • hmmm... it's related, but since even the answer is different, I believe it's not a duplicate. – JackDev Jul 08 '14 at 06:58

2 Answers2

3

That's what connect by is for:

select *
from employee 
where level <= 5
connect by prior id = .manager
start with username = 'abc'

Note that level starts counting from 1 at the row identified with start with. It is not the "overal" hierarchy level of the tree.

More details in the manual:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries003.htm#SQLRF52332

0
select e1.*
from employee e2
left outer join 
employee e1  on e1.id= e2.manager 
where e2.username='abc'
union 
select e.* from employee e where e.username='abc'
Vikram Jain
  • 5,498
  • 1
  • 19
  • 31