You could use a hierarchical query with an inline view that joins the relevant tables together first. The query for the inline view could be:
select p.person_id, pn.first_name, c.person_father_id
from person p
join person_name pn on pn.person_id = p.person_id
left join children c on c.person_child_id = p.person_id;
PERSON_ID FIRST_NAME PERSON_FATHER_ID
---------- ---------- ----------------
2 Lautaro 1
3 Lucas 2
4 Franco 3
5 Agustín 4
1 Santiago
and with that as the basis for a hierarchical query:
select trim(sys_connect_by_path(first_name, ' ')) as whole_name
from (
select p.person_id, pn.first_name, c.person_father_id
from person p
join person_name pn on pn.person_id = p.person_id
left join children c on c.person_child_id = p.person_id
)
where connect_by_isleaf = 1
start with person_id = 4
connect by person_id = prior person_father_id;
WHOLE_NAME
--------------------------------------------------
Franco Lucas Lautaro Santiago
Or you could make the hierarchical query itself a further subquery, and then join to the names afterwards and aggregate that:
select listagg(pn.first_name, ' ') within group (order by lvl) as whole_name
from (
select person_id, level as lvl
from (
select p.person_id, c.person_father_id
from person p
left join children c on c.person_child_id = p.person_id
)
start with person_id = 4
connect by person_id = prior person_father_id
) t
join person_name pn on pn.person_id = t.person_id;
WHOLE_NAME
--------------------------------------------------
Franco Lucas Lautaro Santiago
Notice that with both of those you have to join the tables before you can filter based on your starting ID (start with rather than where, as this is hierarchical). That means that with larger tables it could end up doing more work than you really need or expect.
Or you could do the same thing with recursive subquery factoring (a recursive CTE) if you prefer, and you are using Oracle 11gR2 or higher:
with r (person_id, person_father_id, lvl) as (
select p.person_id, c.person_father_id, 1
from person p
left join children c on c.person_child_id = p.person_id
where p.person_id = 4
union all
select p.person_id, c.person_father_id, r.lvl + 1
from r
join person p on p.person_id = r.person_father_id
left join children c on c.person_child_id = p.person_id
)
select listagg(pn.first_name, ' ') within group (order by lvl) as whole_name
from r
join person_name pn on pn.person_id = r.person_id;
WHOLE_NAME
--------------------------------------------------
Franco Lucas Lautaro Santiago
which looks more complicated but can at least put the filter in the anchor member of the recursive CTE.
Read more about hierarchical queries and recursive subquery factoring.
