StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSingle hierachical query for both all ancestors/parents and children (DB2/SQLServer)
text
Body
copied!<p><br> I have found the solution for Oracle using UNION ALL on two hierarchical CONNECT BY queries, one fetching the ancestors and another the children.<br> I want to achieve the same for both <strong>DB2</strong> and <strong>SQL Server</strong>.<br> I know one element it could be a root, branch or leaf on the hierarchy. I need to fetch its whole hierarchy.</p> <p>Suppose I have <em>itemid='item3' and class='my class'</em>, I need to find its ancestors and children, I came up with:</p> <pre><code>with ancestor (class, itemid, parent, base, depth) as ( select root.class, root.itemid, root.parent, root.itemid, 0 from item root where root.class = 'myclass' and root.itemid = 'item3' -- union all -- select child.class, child.itemid, child.parent, root.base, root.depth+1 -- from ancestor root, item child -- where child.class = root.class -- and child.parent = root.itemid union all select parent.class, parent.itemid, parent.parent, parent.itemid, root.depth-1 from ancestor root, item parent where parent.class = root.class and parent.itemid = root.parent ) select distinct class, itemid, parent, base, depth from ancestor order by class, base, depth asc, itemid </code></pre> <p>I want result like this:</p> <pre><code>class itemid parent base depth myclass item1 null item3 -2 myclass item2 item1 item3 -1 myclass item3 item2 item3 0 myclass item4 item3 item3 1 myclass item5 item5 item3 2 </code></pre> <p>If the above SQL is run I get the ancestors fine. Now if I remove the comments it seems to be on a infinite loop. There must be a way to make that work.<br> I am able to get the results in hierarchy one direction (ancestor or children) fine, but I am unable to get both on a single query.<br> Did anyone ever tried something like that? </p> <p>Thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload