Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.