This is a very general question. I found some questions and discussions on more specific problems on SO, but I am quite sure, that many of you have already solved this one:
input:
x.question:
How can I get the root of the tree of x?
I found out that there should be a way to implement this recursively, but I couldn't achieve it yet.
The root element can be found in the same way as child elements of a given root,
but the query must search in the opposite direction.
Take a look at simple demo: --> http://www.sqlfiddle.com/#!17/fdc8a/1
This query retrieves all childrens of a given root:
WITH RECURSIVE childs( id, parent_id )
AS (
-- get parent
SELECT id, parent_id
FROM tab
WHERE id = 10
UNION ALL
-- get all children
SELECT t.id, t.parent_id
FROM childs c
JOIN tab t
ON t.parent_id = c.id
)
SELECT * from childs;
and this query retrieves all parents of a given child node:
WITH RECURSIVE parents( id, parent_id )
AS (
-- get leaf children
SELECT id, parent_id
FROM tab
WHERE id = 14
UNION ALL
-- get all parents
SELECT t.id, t.parent_id
FROM parents p
JOIN tab t
ON p.parent_id = t.id
)
SELECT * from parents
-- WHERE parent_id is null;
if only the root node is needed, a clause WHERE parent_id IS NULL filters out all except the root.
