Re: Getting to grips with Recursive CTEs.

You are not using a recursive function because it is needed to call the
table "rcte" in the UNION ALL

Here we are using it

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 rcte t3 ON t2.parent >= t3.parent

)

But it will fail because t3.parent is null at the beginning, so try this
another one

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

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

)

I haven't tried.

El vie., 20 sept. 2019 a las 19:34, Pól Ua Laoínecháin (<linehanp(at)tcd(dot)ie>)
escribió:

> 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;
>
>
>

--
________________________________________
Lo bueno de vivir un dia mas
es saber que nos queda un dia menos de vida