Getting to grips with Recursive CTEs. - Mailing list pgsql-novice
From | Pól Ua Laoínecháin |
---|---|
Subject | Getting to grips with Recursive CTEs. |
Date | |
Msg-id | CAF4RT5SriGzjQPawm9=mbOjhxstudF8-8e80WDyPYT-jnYvcRQ@mail.gmail.com Whole thread Raw |
Responses |
Re: Getting to grips with Recursive CTEs.
("Miguel Beltran R." <yourpadre@gmail.com>)
Re: Getting to grips with Recursive CTEs. (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-novice |
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;
pgsql-novice by date: