Oracle SQL: start with … connect by example: ancestor descendant

Create table

First, we need a table to store the data.

create table parent_child(
  parent_ varchar2(20),
  child_  varchar2(20)
);

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/ancestor-descendant/create-table.sql

Fill the table

Then we can insert the data into the table.

Note: each child record has the same value as the parent plus another character.

insert into parent_child values (null,  'a')

insert into parent_child values (  'a',  'af');
insert into parent_child values (  'a',  'ab');
insert into parent_child values (  'a',  'ax');

insert into parent_child values ( 'ab', 'abc');
insert into parent_child values ( 'ab', 'abd');
insert into parent_child values ( 'ab', 'abe');

insert into parent_child values ('abe','abes');
insert into parent_child values ('abe','abet');

insert into parent_child values ( null,   'b');

insert into parent_child values (  'b',  'bg');
insert into parent_child values (  'b',  'bh');
insert into parent_child values (  'b',  'bi');

insert into parent_child values ( 'bi', 'biq');
insert into parent_child values ( 'bi', 'biv');
insert into parent_child values ( 'bi', 'biw');

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/ancestor-descendant/insert-data.sql

A procedure to check if the records are in an ancestor descendant relationship

This procedure takes two ids and prints if these ids stand in an ancestor descendant relationship.

The select statement first evaluates the start with and connect by conditions to build an »intermediate« record set. Then, after creating this intermediate record set, it evaluates the where clause.

If it finds a record that matches the id of descendant, it has found an ancestor descendant relationship, otherwise, it's not an ancestor descendant relationship.

create procedure is_ancestor_descendant(ancestor in varchar2, descendant in varchar2) as
  cnt number;
begin
  select
    count(*) into cnt
  from
    parent_child
  where
    child_  = descendant
  start with
    parent_ = ancestor
  connect by 
    prior child_ = parent_;

  if cnt > 0 then
     dbms_output.put_line(ancestor || ' is an ancestor of ' || descendant);
  else
     dbms_output.put_line(ancestor || ' is no ancestor of ' || descendant);
  end if;

end;
/

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/ancestor-descendant/procedure.plsql

Executing the procedure

exec is_ancestor_descendant('ab', 'abet');
exec is_ancestor_descendant('b' , 'bi'  );
exec is_ancestor_descendant('a' , 'biq' );

Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/ancestor-descendant/try.sql

The output is:

ab is an ancestor of abet
b is an ancestor of bi
a is no ancestor of biq

Cleaning up

Finally, we can clean up

drop table parent_child purge;
drop procedure is_ancestor_descendant;