Databases
StackOverflow2013
Postsdbo
POHow to get the last parent from table for the following scenario in SQL Server 2000?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get the last parent from table for the following scenario in SQL Server 2000?
    text
    copied!<p>I have the following scenario of a tree in SQL Server 2000. </p> <p>There is database with two tables, figuratively speaking </p> <p><code>Table1</code> (Row_Id <em>int</em>, Id <em>char(9)</em>, etc.) </p> <p><em>AND</em> </p> <p><code>Table2</code> (Row_Id <em>int</em>, Parent_Id <em>char(9)</em>, Parent_Parent_Id <em>char(9)</em>, etc).</p> <p><code>Parent_Id</code> in <code>Table2</code> refers to <code>Id</code> in <code>Table1</code>. </p> <p><code>Parent_Parent_Id</code> in <code>Table2</code> refers to <code>Id</code> in <code>Table1</code> too (thus child can have more than one parent).</p> <p>For example, let's consider the tables with some data: </p> <p><strong>Table1</strong> </p> <pre><code>Row_Id Id 1 a 2 b 3 c 4 d 5 e 6 ... </code></pre> <p><strong>Table2</strong> </p> <pre><code>Row_Id Parent_Id Parent_Parent_Id 1 a b 2 b с 3 c d 4 d e 5 ... ... </code></pre> <p>This scenario with data shows that element with <code>Id</code> 'a' from <code>Table1</code> has no more parents and that the last parent of the element with <code>Id</code> 'a' is 'e'. </p> <p>The other words I would like to write stored procedure with input parameter <code>inId</code> (it is any <code>Id</code> from <code>Table1</code>) and as result I would like to get the <strong>last parent who has no more parents</strong>.</p> <p>Now I do this via loop with </p> <pre><code>SELECT ... FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Parent_Id WHERE Table1.Id = inId </code></pre> <p>until I get <code>NULL</code> in the right.</p> <p>How do you think is there any better way to do it?</p> <p>Thank you.</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