Oracle get column of a record depending on another column of the same record

Question

I'm trying to retrieve a record depending on a column of the same record. I have hierarchy fields in my table (with parent and grand parent) and I would like to retrieve data of the same table with id of the parent or grandparent:

SELECT 
O.ID,
O.CODE,
O.LEVEL, 
O.PARENT_ID,
O.PARENT_CODE,
parent.LEVEL AS PARENT_LEVEL,
FROM Organisation_T O
INNER JOIN Organisation_T NT parent on parent.ID = O.ID
WHERE parent.ID = O.ID

But this query is not working.

Here my data:

ID     | CODE    | LEVEL | PARENT_ID
5953   | COMPANY |    1  |  1
230928 | DEP 1   |    2  |  5953
5234   | DEP 2   |    2  |  5953        
7246   | A       |    3  | 5234        
7285   | A.1     |    4  | 7246             
7286   | A.2     |    4  | 7246             
240961 | A.3     |    4  | 7246            
243928 | C       |    3  | 5234 
7287   | D       |    4  | 243928

Here that I want:

ID     | CODE    | LEVEL | PARENT_ID | PARENT_CODE | PARENT_LEVEL
5953   | COMPANY |    1  |  1        | null        | null            
230928 | DEP 1   |    2  |  5953     | COMPANY     | 1
5234   | DEP 2   |    2  |  5953     | COMPANY     | 1           
7246   | A       |    3  | 5234      | DEP 2       | 2        
7285   | A.1     |    4  | 7246      | A           | 3               
7286   | A.2     |    4  | 7246      | A           | 3               
240961 | A.3     |    4  | 7246      | A           | 3               
243928 | C       |    3  | 5234      | DEP 2       | 2   
7287   | D       |    4  | 243928    | C           | 3 

         

Could you help me please with that ?

Answer 1

The join condition was incorrect use INNER JOIN Organisation_T NT parent on parent.ID = *O.Parent_ID*

For T-SQL you can use, assuming the example data is correct:

SELECT 
O.ID,
O.Code,
O.Level,
parent.Id as Parent_Id,
parent.Code as Parent_Code,
parent.Level as Parent_Level
FROM Organisation_T O
INNER JOIN Organisation_T parent on parent.ID = O.Parent_Id