Search code examples
referenceself

ORACLE - SQL - SELF REFERENCE IN TABLE

I have Table ( TABLE A ) like below,

ID  INDEX_ID  
1   1  
2   1.1  
3   1.1.1  
4   2
5   3
6   3.1
7   3.2

I am looking to create result set in such a way like 

INDEX PARENT PARENT_ID  
1     0      0  
1.1   1      1   
1.1.1 1.1    2  
2     0      0  
3     0      0  
3.1   3      5  
3.2   3      5

Would some one please give some idea to do arrive with this result set by using directly SQL ?

Regards, Nandy

Solution

  • Use a self-join:

    SELECT t1.INDEX_ID AS INDEX,
       COALESCE(t2.INDEX_ID, '0'),
       COALESCE(t2.ID, '0')
FROM TABLEA t1
LEFT JOIN TABLEA t2
    ON t1.INDEX_ID = SUBSTR(t2.INDEX_ID, 1, INSTR(t2.INDEX_ID, '.', -1) - 1)