Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (parent_id INT, id INT);
CREATE TABLE table2 (id INT, name VARCHAR(255));
INSERT INTO table1 VALUES (2,1);
1 rows affected

INSERT INTO table1 VALUES (1,2);
1 rows affected

INSERT INTO table1 VALUES (2,3);
1 rows affected

INSERT INTO table2 VALUES (1, 'дерево');
1 rows affected

INSERT INTO table2 VALUES (2, 'железо');
1 rows affected

INSERT INTO table2 VALUES (3, 'пластик');
1 rows affected

WITH
cte (parent_id, id) AS ( SELECT * 
                         FROM table1
                       UNION ALL
                         SELECT cte.parent_id, table1.id 
                         FROM cte
                         JOIN table1 ON cte.id = table1.parent_id
                         WHERE cte.parent_id <> table1.id )
SELECT t21.name, t22.name
FROM cte
JOIN table2 t21 ON cte.parent_id = t21.id
JOIN table2 t22 ON cte.id = t22.id;


NAME
NAME


железо
дерево

дерево
железо

дерево
пластик

железо
пластик

NAME	NAME
железо	дерево
дерево	железо
дерево	пластик
железо	пластик