Re: recursive WITH nested union ALL with NOCYCLE logic

On Fri, Mar 18, 2016 at 2:32 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
> ​
>
> ​
> ​
>
David,
If I am understanding you correctly, you are assuming that alternative
hierarchies are mapped to only ROOT level hierarchies. It's a reasonable
assumption on your part given my illustration only covered this use case.
But lets add another mapping.

insert into mike_map (key,child,parent) values
('555','kkk','bbb');
This creates an alternative hierarchy that branches from a CHILD (bbb) of
the ROOT (aaa) hierarchy.
So I think I still need the UNION ALL inside the recursive part.