i want to get just the end parent for each child using oracle connect by , start with statement

Question

I am using start with , connect by statement to get data recursivly, i am getting all parent - child but i just want to get the end parent for each child. for eg i have following data

child --> parent
a ------> b,
b ------> c,
c ------> d,
c ------> e

so i want the output just

a --> d,
and a --> e

my query is

SELECT  LEVEL, cp.child, cp.parent FROM child_parent cp
CONNECT BY nocycle PRIOR cp.parent= cp.child
START WITH cp.child= a

can any body help me with this.

René Nyffenegger · Answer 1 · 2011-01-21T15:28:34.733

0

It's unclear whether you want to hard code your root 'a' (which makes the solution a bit easier) or if you want to have a more generic solution for multiple roots. Assuming the former, then this might get you going:

create table child_parent (
  parent varchar2(2),
  child  varchar2(2)
);

insert into child_parent values (null, 'a');
insert into child_parent values ( 'a', 'b');
insert into child_parent values ( 'b', 'c');
insert into child_parent values ( 'c', 'd');
insert into child_parent values ( 'c', 'e');
insert into child_parent values (null, 'k');
insert into child_parent values ( 'k', 'l');
insert into child_parent values ( 'l', 'm');
insert into child_parent values ( 'l', 'n');

with choose_root_here as (
  select 'a' as root from dual
)
select
  choose_root_here.root || '->' || child from
(
  select 
    level             lvl, 
    connect_by_isleaf leaf,
    cp.parent,
    cp.child  
  from 
    child_parent cp 
  connect by nocycle prior cp.child= cp.parent
  start with 
    cp.child='a'
)
cross join choose_root_here
where
  leaf = 1;

If you want a more generic solution for any root, the this might help

Edit, for parent cannot be null:

select
  substr(regexp_replace(path, '##(\w+).*##(\w+)', '\1->\2'),1,30) path
  from (
      select 
        level             lvl, 
        substr(sys_connect_by_path(child, '##'),1,20) path,
        connect_by_isleaf leaf,
        cp.parent,
        cp.child  
      from 
        child_parent cp 
      connect by nocycle prior cp.child= cp.parent
      start with 
        cp.parent is null
  )
  where leaf = 1;

edited Jan 21 '11 at 15:28

answered Jan 20 '11 at 23:09

René Nyffenegger

39,402
33
158
293

thanks for reply and sorry about not making it much clear. i dnt want to hard code it for specific value i want to make it general. one more thing i cant enter value null in my table as you did "insert into child_parent values (null, 'a');". – sandeep dhaliwal Jan 20 '11 at 23:28
If you can't enter parents with a null, how do you identify parents? – René Nyffenegger Jan 20 '11 at 23:34
Just at looking at the column values. if there is no row that shows parent for 'a' it means 'a' has no parent – sandeep dhaliwal Jan 21 '11 at 00:00
the solution which you provided gives the output b->d, b->e, l->m , l->n .but i want the end parent of each child like (child->parent )n->k, m->k, l->k, e->a, d->a, c->a, b->a How can i get this? – sandeep dhaliwal Jan 21 '11 at 15:30
If I do my inserts and the run the second query, it does return a->d a->e k->m k->n – René Nyffenegger Jan 21 '11 at 15:30
are you inserting the null values? – sandeep dhaliwal Jan 21 '11 at 15:33
I ran it with the `create table` and `insert ... into` that I have provided. – René Nyffenegger Jan 21 '11 at 16:03
you are inserting null value "insert into child_parent values (null, 'a'); " but i cant insert null in my table. – sandeep dhaliwal Jan 21 '11 at 17:16

i want to get just the end parent for each child using oracle connect by , start with statement

1 Answers1