I'm not sure I understand the requirements fully.
For example, what should be the output if the input is S? The same as T or something else? And what happens if there's an edge between T and P?
Also B has B -> A and B -> C, so why do you only want B -> C if this is the input?
In any case, here's an outline of a solution which should get you started:
- From the input node, try and go up and down the tree according to the relationship types
- When going down, the overall parent is the root
- When going up, the overall parent is the leaf (what happens if a path has many leaves?)
- While doing so, exclude any edges with the same vertices as the current going in the other direction
- Union the results of the two trees together
- Find the overall parent by taking:
- The final leaf from the UP branch if present
- The down root if there's no up
You can do the final operation by using last_value, sorting the rows first by UP/DOWN, then by reverse order for the UP and tree order for DOWN:
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;
This mostly gives the output you requested (though input B and you'll get B -> A and B -> C) and I've guessed that when passing S, R, etc you want the same output as T.
The connect by clauses are likely to be specific to the data you've passed though; I'm unconvinced these will generalize to your full data set.