Table tMain:
+----+-------+---------+ | ID | name | id_ref | +----+-------+---------+ | 1 | amine | 4 | | .. | | | +----+-------+---------+
Table tTree:
+----+--------+-----------+ | ID | name | id_parent | +----+--------+-----------+ | 1 | root | null | | 2 | child1 | 1 | | 3 | child2 | 2 | | 4 | child3 | 3 | +----+--------+-----------+
So I have to tables which i would like to "connect". Table "tMain" contains some data and a foreign key referencing the tTree table (reference to the lowest child item). What I would like is to use one query to get all the data from tMain together with the assembled path from tTree. It would look like this:
+----+-------+--------+---------------------------+ | ID | name | id_ref | Path | +----+-------+--------+---------------------------+ | 1 | amine | 4 | root/child1/child2/child3 | | .. | | | | +----+-------+--------+---------------------------+
WITH tree$ AS (
SELECT T.id, ltrim(sys_connect_by_path(name, '/'), '/') AS path
FROM tTree T
START WITH id_parent IS NULL
CONNECT BY PRIOR ID = id_parent
)
SELECT T.id, M.name, M.id_ref, T.path
FROM tMain M
JOIN tree$ T ON T.id = M.id_ref
;