Help me create a hierarchical query.
I have two table:

Table 1(dic_kgs)

1                           A  
2              1            A0
3              1            A1
4                           B

and so on

Table 2(dic_kgs_strings)

1            1        Name1
2            1        Name2
3            1        Name3 
4            1        Name4

and so on.

I want to get result in the follow view:

A Name1
A0 Name2
A1 Name3
B Name4

I have writen only part of query:

select kgs_code
  from dic_kgs 
  start with dic_kgs.kgs_parent_id is null
      connect by prior dic_kgs.kgs_id = dic_kgs.kgs_parent_id;  


2 Answers 2


Assuming kgs_id is the primary key of both tables (which is a little strange), join the tables before doing the connect by:

select kgs_code, kgs_name
from (select * from dic_kgs join dic_kgs_strings using(kgs_id))
start with kgs_parent_id is null
connect by kgs_parent_id = prior kgs_id; 

alternatively look up kgs_name after the connect by (this approach can be helpful in more complicated situations such as if not all records in dic_kgs have a corresponding record in dic_kgs_strings):

select kgs_code, ( select kgs_name 
                   from dic_kgs_strings 
                   where kgs_id=kgs_code.kgs_id ) as kgs_name
from dic_kgs
start with kgs_parent_id is null
connect by kgs_parent_id = prior kgs_id; 

Conventional joins seem to be working just as as well in your case.

try the following query:

select kgs_code, kgs_name
  from so_kgs join so_kgs_strings using(kgs_id)
  start with kgs_parent_id is null
      connect by prior kgs_id = kgs_parent_id;  

It seems to me just a little bit nicer and readable than the ones proposed in Jack Douglas's answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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