How do I get value from parent row

Question

I have two tables.

One contains a hierarchical structure of topics and another table that contains optional configuration of that topic, that controls it's visibility in several contexts (one column for each).

Let's call these tables Topic and Conf respectively. The relationship between Topic and Conf tables is 1:0..1, (a particular topic might have a row for it's respective conf or not, in which case it inherits conf from it's parent topic)

Question: How can I create a query that will list all visibility options for all topics (including those inherited from parent conf)?

I do have an idea how to create a hierarchical query of topics using START WITH ... and CONNECT BY ... clauses, but I do not know how to promote values from parent row.

Answer 1

May be this will help.

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic, NVL(a.configuration,CONNECT_BY_ROOT configuration) as conf from topicdet a
connect by nocycle prior a.topic=a.parenttopic;

if you dont want to use hierarchical query then

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic,case when a.configuration is null then (select b.configuration from topicdet b where b.parenttopic=a.topic) else a.configuration end as config from topicdet a