Hi Chris,

Table T stores parts relationships of four types - Unilateral, Bilateral, Marketing/Promotional, and KITs, and the requirement is to identify the items relationship chain.

The relation type 'S' means superseded and is combined with relation direction 'O' unilateral (i.e. A -> B -> C) and 'T' bilateral (D -> E <-> F)

The relation type 'M' are multiple parts required for marketing/promotional purposes. Relation direction doesn't matter here and there can be only one parent in such relation (Y -> Y1, Y -> Y2).

The last relation is identified by the component column having a value 'C' meaning the component type of relation, relation direction doesn't matter here, and there can be only one parent in such relation.

SELECT 'X', 'S' , 'X1', NULL, 'C' FROM DUAL UNION ALL i.e. A row with relation_type 'S' and component as 'C' denotes a major part of the KIT.

SELECT 'X', 'R' , 'X2', NULL, 'C' FROM DUAL UNION ALL i.e. A row with relation_type 'R' and component as 'C' denotes a minor part of the KIT.

X -> X1

X -> X2
An "item" is either a kit, recognized by relation type 'M' or by component = 'C', or else it is a "single item" (recognized by relation type different from 'M' and null component. In the case of kits, I want the query to return only the immediate children (direct descendants), while for "single items" I want to see the entire hierarchy of descendants.

In a unilateral relationship, you can only move forward in the chain but in a bilateral relation, you must go a step back and check if the item in the previous step also has bilateral relation, and if yes again go a step back until you reach a unilateral relation and then start moving forward e.g. E <> F.

Expected output -

When passed item A as parameter then -

parent child

A B

A C B to A row should be ignored here because that is an incorrect recursive relationship.

When passed item B as parameter then -

parent child

B C

When passed item D as parameter then -

parent child

D E

D F

When passed item E as parameter then -

parent child

E F

When passed item F as parameter then (because of E <--> F traverse back)-

parent child

E F

When passed item Y as parameter then (Check the only row of relation type M for the part)-

parent child

Y Y1

Y Y2

When passed item X as parameter then (Check the only row of the component as C for the part)-

parent child

X X1

X X2

The sample data and code in the live SQL link give desired results except in the below two cases which I want to manage in solution.

Case 1 - When the is full bilateral relation P <> Q <> R <> S <> T, the expectation is as below when you pass T item -

Parent Child
P Q
P R
P S
P T


How to add order by in output?
How to remove the distinct logic from the query in any way?

with up ( rt, nd, lvl, direction ) as (
  select  case when connect_by_isleaf = 1 then v1 end rt,
          v2, level, 'UP' direction
  from    t 
  where   connect_by_iscycle = 0 
  start   with v2 = :item 
  and   relation_direction = 'T' 
  and ( v1, v2 ) not in ( select v2, v1 from t )
  connect by nocycle relation_direction = 'T' 
  and prior v1 = v2 
  and ( v1, v2 ) not in ( select v2, v1 from t )
), down ( rt, nd, lvl, direction ) as (
  select  connect_by_root(v1), v2, level, 'DOWN' direction
  from    t 
  where   connect_by_iscycle = 0 
  start   with v1 = :item 
  connect by nocycle prior relation_type != 'M' 
  and prior component is null 
  and v1 = prior v2 
  and ( v1, v2 ) not in ( select v2, v1 from t )
), tree as (
  select * from down
  union all
  select * from up
)
  select t.*, 
         first_value (
           rt
         ) over ( 
            order  by case 
              when direction = 'UP' then 1 else 2 
            end, case 
              when direction = 'UP' then -lvl else lvl
            end    
         ) parent
  from   tree t
  order  by case 
    when direction = 'UP' then 1 else 2 
  end, case 
    when direction = 'UP' then -lvl else lvl
  end;