Parent child relationship Join tables in oracle sql

I have a requirement below.

Now I have to get output like below

How can this be achieved ? I have written the below SQL but parent_position_id is coming, not parent_position_code

select
hapf.position_code,
pphf.parent_position_id
from
hr_all_positions_f hapf, PER_POSITION_HIERARCHY_F pphf
where
hapf.position_id = pphf.position_id

Should I write a sub query? How should I proceed ? This is Oracle SQL

Thanks, Shivam

Answer

Noone ever said you could only join a table in once:

select
  chi.position_code,
  par.position_code as parent_position_code
from
  hr_all_positions_f hapf
  INNER JOIN PER_POSITION_HIERARCHY_F chi on hapf.position_id = chi.position_id
  INNER JOIN PER_POSITION_HIERARCHY_F par on hapf.parent_position_id = par.position_id

Bear it in mind; I see people coming to thinking all the time that they can only join a table once. If one table decodes a value in 3 different columns, then you sure can join that same table in 3 times… Imagine if it were an address table, and a Student had a HomeAddressId, WorkAddressId and StudyAddressId, and the Address table held all these addresses – you’d join the addresses table to the Student table 3 times to get all the data..