Re: recursive WITH nested union ALL with NOCYCLE logic

On Fri, Mar 18, 2016 at 2:06 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

> I have two tables, 1 is a hierarchical table and the other a map to
> alternative hierarchies. Given a starting node, I need to be able to return
> the hierarchy and all related hierarchies.
>
> with
> recursive inn_t(keyv, val, parent) as (
> select * from (
> select key as keyv, val, parent
> from mike_hier hi where hi.key ='aaa'
> union all
> -- get all alt hierarchies
> select child ,null ,null from mike_map ma where ma.parent
> ='aaa' ) gg
> union all
> (
> with xxx as ( select * from inn_t i ) -- only a single reference
> allowed to inn_t
> select * from
> (
> select mh.key , mh.val , mh.parent
> from mike_hier mh
> where mh.parent in (select keyv from xxx) -- normally would
> join inn_t
> union all
> select child ,null ,null
> from mike_map ma
> where ma.parent in (select keyv from xxx) -- normally would
> join inn_t
> ) unionall
> )
> )
> select distinct * from inn_t where val is not null;
>
>
Where should I send the bill for the pain relievers :)

​with recursive --applies to the second CTE really but placed at the top by
convention (maybe by rule)
inn_t(keyv, val, parent) as ( --not recursive, no reference to inn_t in
this CTE
-- Given a base tree lets return all rows where it is the primary...
select * from (
select key as keyv, val, parent
from mike_hier hi where hi.key ='aaa'
-- ...as well as the primary rows for any of its alises (derived though
they may be it should work)
union all
-- get all alt hierarchies
select child ,null ,null from mike_map ma where ma.parent
='aaa' ) gg
), recurse_here_instead AS (
-- Now for each of the those primary rows locate in mike_heir locate the
direct descendants
-- and add them to the working set. On the next pass the original parents
will be skipped
-- because they were already processed but all of these newly added
children will be
-- put through the wringer to find their children.
select * from inn_t i --initial condition is a complex query so
simplify the recursive portion by referecing a CTE
UNION ALL
select mh.key , mh.val , mh.parent
from mike_hier mh
join inn_t ON (mh.parent = inn_t.keyv)
)
-- got rid of distinct...honestly not positive why but I suspect if you
write the query correct DISTINCT on the outer layer should
-- be redundant.
select * from recurse_here_instead where val is not null;

​I haven't yet coded a variation of this query that used the path array and
cycle-avoidance logic so I'm leaving that open for the moment. Now that
this is written more correctly incorporating that from other's examples
should be easier.

David J.
​

​
​