Hi Gábor!

I see, you have many root elements.
Then Mark gave the perfect solution:

WITH RECURSIVE tree AS (
SELECT t.id, t.parent_id
FROM table1 t
inner join table1 root on root.id = t.parent_id
WHERE root.parent_id is null
UNION ALL
SELECT m.id, t.parent_id
FROM table1 m
JOIN tree t ON t.id = m.parent_id
WHERE m.parent_id is not null
ORDER BY m.id
)
select r.id, r.parent_id
from table1 r
where r.parent_id is null
union all
SELECT t.id, t.parent_id
FROM tree t

(I prefer join instead of exists, it is better when you have good indicies.)

The result is:
ID PARENT_ID
1
7
2 1
3 1
4 1
5 1
6 1
8 7
9 7
10 7
11 7
12 7

András



__________ Information from ESET Mail Security, version of virus signature database 17471 (20180530) __________

The message was checked by ESET Mail Security.
http://www.eset.com