I have this working CTE example.
I can select all grand-parents and all children.
But how can I select all grand-parents and all children in one statement?
In this example I want Grandfather, Father, Son as output if I give "Father" as input.
I use PostgreSQL. But I think this question should be standard SQL.
Please correct me if I use PostgreSQL specific syntax.
DROP table if exists tree;
CREATE TABLE tree (
id SERIAL PRIMARY KEY,
name character varying(64) NOT NULL,
parent_id integer REFERENCES tree NULL
);
insert into tree values (1, 'Grandfather', NULL);
insert into tree values (2, 'Father', 1);
insert into tree values (3, 'Son', 2);
-- -------------------------------------
-- Getting all children works
WITH RECURSIVE rec (id) as
(
SELECT tree.id, tree.name from tree where name='Father'
UNION ALL
SELECT tree.id, tree.name from rec, tree where tree.parent_id = rec.id
)
SELECT *
FROM rec;
-- Result:
-- id | name
-- ----+--------
-- 2 | Father
-- 3 | Son
-- -------------------------------------
-- Getting all parents works
WITH RECURSIVE rec (id) as
(
SELECT tree.id, tree.name, tree.parent_id from tree where name='Father'
UNION ALL
SELECT tree.id, tree.name, tree.parent_id from rec, tree where tree.id = rec.parent_id
)
SELECT id, name
FROM rec;
-- Result
-- id | name
-- ----+-------------
-- 2 | Father
-- 1 | Grandfather
Update
Above is a simplified working example. The tree can be up to 100 level deep. There can be several level of ancestors above "Father" and several level of descendants below. I want all ancestors and all descendants.
Best Answer
If you want all ancestors and all descendants, you can combine the two queries in one. Use the two CTEs and then a simple
UNION
:If we haven't got any errors above, we can them improve it:
UNION
toUNION ALL
by putting the starting node(s) in only one of the CTEs.JOIN .. ON
instead of the implicit joins.The query becomes: