Re: recursive WITH nested union ALL with NOCYCLE logic

On Fri, Mar 18, 2016 at 2:06 PM, Michael Moore <michaeljmoore@gmail.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.

​

​

​