CODEWITHSUNDEEP
sqloracle-databaserecursive-query
DMA VE
DMA VE

Reputation: 113

Oracle CONNECT BY recursive and return value a match

In the following example:

TABLE

ID  NAME    ATTR
-----------------
1   A1      ROOT
2   A2  
3   A3      VALX
4   A4  
5   A5  
6   A6

RELATIONSHIP

ID  CHILD_ID    PARENT_ID
-------------------------
1      6            4
2      5            4
3      4            3
4      3            1
5      2            1

SCHEMA

SCHEMA

I need a query to get the value of the ATTR column of the PARENT when it is different from null. Raising the levels until you get the first match. For example with ID 6:

ID   NAME    NAME_PARENT     ATTR_PARENT
-----------------------------------------
6     A6          A3            VALX

I have tried with:

select T.ID, T.NAME, T2.NAME PARENT_NAME, T2.ATTR ATTR_PARENT
from TABLE T
INNER JOIN RELATIONSHIP R
ON R.CHILD_ID = T.ID
INNER JOIN TABLE T2
ON T2.ID = R.PARENT_D
WHERE T2.ATTR IS NOT NULL
START WITH T.ID = 6
CONNECT BY T.ID = PRIOR R.PARENTID 
--and R.PARENTID != prior T.ID

And sorry for my bad english

Upvotes: 1

Views: 252

Answers (2)

The Impaler
The Impaler

Reputation: 48865

Instead of using the [mostly obsolete] CONNECT BY clause you can use standard Recursive SQL CTEs (Common Table Expressions).

For example:

with
n (id, name, name_parent, attr_parent, parent_id, lvl) as (
  select t.id, t.name, b.name, b.attr, r.parent_id, 1
  from t
  join r on t.id = r.child_id
  join t b on b.id = r.parent_id
  where t.id = 6 -- starting node
 union all 
  select n.id, n.name, b.name, b.attr, r.parent_id, lvl + 1
  from n
  join r on r.child_id = n.parent_id
  join t b on b.id = r.parent_id
  where n.attr_parent is null
)
select id, name, name_parent, attr_parent 
from n
where lvl = (select max(lvl) from n)

Result:

ID  NAME  NAME_PARENT  ATTR_PARENT
--  ----  -----------  -----------
6   A6    A3           VALX

For reference, the data script I used is:

create table t (
  id number(6),
  name varchar2(10),
  attr varchar2(10)
);

insert into t (id, name, attr) values (1, 'A1', 'ROOT');
insert into t (id, name, attr) values (2, 'A2', null);
insert into t (id, name, attr) values (3, 'A3', 'VALX');
insert into t (id, name, attr) values (4, 'A4', null);
insert into t (id, name, attr) values (5, 'A5', null);
insert into t (id, name, attr) values (6, 'A6', null);

create table r (
  id number(6),
  child_id number(6),
  parent_id number(6)
);

insert into r (id, child_id, parent_id) values (1, 6, 4);
insert into r (id, child_id, parent_id) values (2, 5, 4);
insert into r (id, child_id, parent_id) values (3, 4, 3);
insert into r (id, child_id, parent_id) values (4, 3, 1);
insert into r (id, child_id, parent_id) values (5, 2, 1);

Upvotes: 2

user5683823
user5683823

Reputation:

Here is how you can do the whole thing in a single pass of connect by - using the various features available for this kind of query (including the connect_by_isleaf flag and the connect_by_root pseudo-column):

select  connect_by_root(r.child_id) as id,
        connect_by_root(t.name)     as name,
        t.name                      as name_parent,
        t.attr                      as attribute_parent
from    r join t on r.child_id = t.id
where   connect_by_isleaf = 1
start   with r.child_id = 6
connect by prior r.parent_id = r.child_id and prior t.attr is null
;

        ID NAME       NAME_PARENT ATTRIBUTE_PARENT
---------- ---------- ----------- ----------------
         6 A6         A3          VALX

Note that this will still return a null ATTRIBUTE_PARENT, if the entire tree is walked without ever finding an ancestor with non-null ATTRIBUTE. If in fact you only want to show something in the output if an ancestor has a non-null ATTRIBUTE (and allow the output to have no rows if there is no such ancestor), you can change the where clause to where t.attr is not null. In most cases, though, you would probably want the behavior as I coded it.

I used the tables and data as posted in @TheImpaler 's answer (thank you for the create table and insert statements!)

As I commented under his answer: recursive with clause is in the SQL Standard, so it has some advantages over connect by. However, whenever the same job can be done with connect by, it's worth at least testing it that way too. In many cases, due to numerous optimizations Oracle has come up with over time, connect by will be much faster.

One reason some developers avoid connect by is that they don't spend the time to learn the various features (like the ones I used here). Not a good reason, in my opinion.

Upvotes: 1

Related Questions