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.

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

Table 1(dic_kgs)

KGS_ID    KGS_PARENT_ID    KGS_CODE
1                           A  
2              1            A0
3              1            A1
4                           B

and so on

Table 2(dic_kgs_strings)

KGS_ID    LANG_ID    KGS_NAME    KGS_ADD_INFO
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;  

Thanks.

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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; 
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.