Getting to grips with Recursive CTEs.

Hi all,

I'm trying to get to grips with Recursive CTEs.

I have a problem that I can't appear to solve using them.

I have data like this - fiddle here full data + table at the end of
the question and in the fiddle
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=955a33fe6bd63302da5e8801eb7bbd98
or see bottom of this question.


CREATE TABLE tree (parent varchar(10), child varchar(10));

Sample.
('a','b'),
('a','c'),
('b','e'),
('b','f'),

So a is the parent of b and b is the parent of e, so I'd like records like

a, b
a, c
a, e
a, f
and so on - i.e. no matter how far down into the tree you are, I want
to list child x with all its ancestors as separate records.

Now, I'm managed to get this far

WITH rcte (child, parent) AS
(
  SELECT parent AS child, NULL AS parent FROM tree t1
    WHERE Parent NOT IN (SELECT child FROM tree)
  UNION ALL

  SELECT t2.child, t3.parent FROM tree t2
  JOIN tree t3 ON t2.parent >= t3.parent
  -- ORDER BY child
)
SELECT DISTINCT * FROM rcte
ORDER BY parent NULLS FIRST, child;

But that just appears to produce a CROSS JOIN between the parents and
children excluding records where the parent is bigger than the child -
which is obviously not possible.

Sample of my results

parent child
a
m
x
a b
a c

This is OK, but as you can see m and x don't have any parents, but then we have

m n
m y
m z

but the tuple (m,n) is a free standing record and the only one
involving these two variables, so (m, n) is correct but (m,y) and (m,
z) aren't.

I would also like the level to appear in a third column but I can't
seem to get that either. The SQL Server fiddle (same data) here
(https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6c57c670b7f3a7aaa74b08b6bd897652)
shows what I am trying to do - but even this only works for the base
level with a wierd syntax.

I would be particularly grateful for explanations, esp to any URLs &c.
to give me pointers here - I have obviously done my own searches but
these have proved fruitless so far.

Should you require any further information, please don't hesitate to
get back to me here.

TIA and rgs,

Pól...

TABLE, data and query.

CREATE TABLE tree (parent VARCHAR(10), child VARCHAR(10));

INSERT INTO tree VALUES ('a','b'), ('a','c'), ('b','e'), ('b','f'),
('a','d'), ('b','g'),
('c','h'), ('c','i'), ('d','j'), ('f','k'), ('x','y'), ('y','z'), ('m','n');

WITH rcte (child, parent) AS
(
  SELECT parent AS child, NULL AS parent FROM tree t1
    WHERE Parent NOT IN (SELECT child FROM tree)
  UNION ALL

  SELECT t2.child, t3.parent FROM tree t2
  JOIN tree t3 ON t2.parent >= t3.parent

)
SELECT DISTINCT parent, child FROM rcte
ORDER BY parent NULLS FIRST, child;