1

Given:

object_table
------------
ID
Parent

Sample Data:

ID : Parent 
 1  : null
 22 : 1
 45 : 22
 32 : 45

Query using connect by clause:

select * from object_table
start with id = 45
connect by prior parent = id;

Result:

ID : Parent 
 45 : 22
 22 : 1
 1  : null

Is it possible to write a query which gives the same result using only joins and not using connect by clause.

Shashank112
  • 11
  • 1
  • 1
    Yep, since Oracle 11 you can use *standard SQL* recursive CTEs. – The Impaler Jan 29 '20 at 18:05
  • 1
    Yes, but you need a separate `join` for each "level" that you traverse. You need to know the depth in advance. – Gordon Linoff Jan 29 '20 at 18:05
  • It might help to explain *why* you want to avoid a specific feature. A lot of questions that start with "how do I do this without using feature X" are [XY problems](https://meta.stackexchange.com/a/66378/175876). – Jon Heller Jan 29 '20 at 23:44

1 Answers1

3

Yes, since Oracle 11g Release 2 you can use recursive CTEs, instead of old style hierarchical queries. Moreover recursive CTEs are much more flexible since they allow you to walk any kind of graph.

For example:

create table t (
  id number(6),
  parent number(6)
);

insert into t (id, parent) values (1, null);
insert into t (id, parent) values (22, 1);
insert into t (id, parent) values (45, 22);
insert into t (id, parent) values (32, 45);

with n (id, parent, lvl) as (
  select id, parent, 1 from t where id = 45
  union all
  select t.id, t.parent, n.lvl + 1
  from n
  join t on t.id = n.parent
)
select * from n order by lvl

Result:

ID  PARENT  LVL
--  ------  ---
45      22    1
22       1    2
 1  <null>    3
The Impaler
  • 45,731
  • 9
  • 39
  • 76