Databases
StackOverflow2013
Postsdbo
POSQL Server CTE -Find top parentID forEach childID?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server CTE -Find top parentID forEach childID?
    text
    copied!<p>I have a table which contains hierarchy data - something like: </p> <pre><code>childID | parentID ____________________ 1 | 5 5 | 9 9 | 20 2 | 4 3 | 7 7 | 8 8 | 8 20 | 20 4 | 4 8 | 8 </code></pre> <p>desired output: </p> <p><img src="https://i.stack.imgur.com/ztnGl.jpg" alt="enter image description here"></p> <p>I've created a recursive CTE which finds me the top <code>fatherID</code>.</p> <p>Something like: </p> <pre><code>;WITH cte AS ( SELECT a.childID ,a.parentID ,1 AS lvl FROM [Agent_Agents] a WHERE a.childID = 214 //&lt;==== value to begin with !! - thats part the problem UNION ALL SELECT tmp.childID ,tmp.parentID ,cte.lvl+1 FROM [Agent_Agents] tmp INNER JOIN cte ON tmp.childID = cte.parentID WHERE cte.childID&lt;&gt;cte.parentID ) SELECT * FROM cte WHERE lvl = ( SELECT MAX(lvl) FROM cte ) </code></pre> <p><strong>The problem:</strong> </p> <p>I executed the CTE with <strong>explicit</strong> <code>childID</code> value to begin with (214) ! So it gives me the value for 214 only. the CTE do the recursive part and find topParent for childID.</p> <p><strong>but</strong> I want <code>ForEach row in the Table</code> - to execute the CTE with the <code>childID</code> value !</p> <p>I have tried to do it with <code>CROSS APPLY</code>: </p> <p>Something like: </p> <pre><code>select * from myTable Cross Apply ( ;WITH cte AS (....) ) </code></pre> <p>but IMHO (from my testing !!) - its impossible.</p> <p>The other idea of putting the recursive CTE in a UDF has a performance penalty (udf's problem as we know).</p> <p>How can I create this query so that it'll actually work? ( or some near solution )?</p> <p>here is what I've tried </p> <p><a href="https://data.stackexchange.com/stackoverflow/query/edit/69458" rel="nofollow noreferrer">https://data.stackexchange.com/stackoverflow/query/edit/69458</a></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