6

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.

  • If your hierarchy is really only 3 levels deep, you can get children by parent_id IS NOT NULL. Getting the parents is more complicated (involves an EXISTS or similar), but still no need for a recursive CTE (which, in this form, is only implemented in PostgreSQL, AFAIK). – dezso Jun 9 '17 at 9:06
  • @dezso this is just an example. The tree can be up to 100 level deep. – guettli Jun 9 '17 at 9:10
  • @deszo: essentially all modern DBMS support recursive CTEs like that (however Postgres is the only one complying with the standard that states that the keyword recursive is required). The queries in the question are indeed standard ANSI SQL. – a_horse_with_no_name Jun 9 '17 at 9:13
  • If you want only the and children of a specific node, I don't see why you need a recursive CTE. A simple SELECT ..<node>.. UNION SELECT ..<father>.. UNION SELECT ..<children>..; would suffice. Unless you want all ancestrors (parent, grandparent, ..., grand-grand-..-parent) and all descendants (childre, grandchildren, ... etc). – ypercubeᵀᴹ Jun 9 '17 at 9:18
  • @ypercubeᵀᴹ I updated the question: there can be several levels up or several levels below. All of them should be collected. – guettli Jun 9 '17 at 10:10
11

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:

WITH RECURSIVE 
    -- descendants 
    rec_d (id, name) AS
    (
      SELECT tree.id, tree.name FROM tree WHERE name = 'Father'
      UNION ALL
      SELECT tree.id, tree.name FROM rec_d, tree where tree.parent_id = rec_d.id
    ),
    --  ancestors
    rec_a (id, name, parent_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_a, tree WHERE tree.id = rec_a.parent_id
    )
SELECT id, name FROM rec_a
UNION 
SELECT id, name FROM rec_d ;

If we haven't got any errors above, we can them improve it:

  • change the final UNION to UNION ALL by putting the starting node(s) in only one of the CTEs.
  • use JOIN .. ON instead of the implicit joins.
  • fix mismatches between SELECT and CTE column lists.

The query becomes:

WITH RECURSIVE 
    -- starting node(s)
    starting (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t
      WHERE t.name = 'Father'          -- this can be arbitrary
    ),
    descendants (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id 
      FROM starting 
      UNION ALL
      SELECT t.id, t.name, t.parent_id 
      FROM tree AS t JOIN descendants AS d ON t.parent_id = d.id
    ),
    ancestors (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id 
      FROM tree AS t 
      WHERE t.id IN (SELECT parent_id FROM starting)
      UNION ALL
      SELECT t.id, t.name, t.parent_id 
      FROM tree AS t JOIN ancestors AS a ON t.id = a.parent_id
    )
TABLE ancestors
UNION ALL
TABLE descendants ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.