How to get the last parent from table for the following scenario in SQL Server 2000?

Question

I have the following scenario of a tree in SQL Server 2000.

There is database with two tables, figuratively speaking

Table1 (Row_Id int, Id char(9), etc.)

AND

Table2 (Row_Id int, Parent_Id char(9), Parent_Parent_Id char(9), etc).

Parent_Id in Table2 refers to Id in Table1.

Parent_Parent_Id in Table2 refers to Id in Table1 too (thus child can have more than one parent).

For example, let's consider the tables with some data:

Table1

Row_Id  Id 
  1     a  
  2     b  
  3     c  
  4     d  
  5     e  
  6    ...

Table2

Row_Id  Parent_Id  Parent_Parent_Id
  1        a               b
  2        b               с   
  3        c               d
  4        d               e   
  5       ...             ...

This scenario with data shows that element with Id 'a' from Table1 has no more parents and that the last parent of the element with Id 'a' is 'e'.

The other words I would like to write stored procedure with input parameter inId (it is any Id from Table1) and as result I would like to get the last parent who has no more parents.

Now I do this via loop with

SELECT ... 
FROM Table1 
LEFT JOIN Table2 ON Table1.Id = Table2.Parent_Id 
WHERE Table1.Id = inId

until I get NULL in the right.

How do you think is there any better way to do it?

Thank you.

Answer 1

In SQL Server versions after 2000 there are several ways to to this efficiently. In SQL 2000 however, you are stuc resolving this manually. There are two options:

• a recursive function or procedure
• a loop

If the loop is written correctly, it is most likely going to be the faster of the two. Just make sure you have appropriate indexes on the tables.

You also don't need to join to table1. just do something like SELECT @newParent = Parent_Parent_Id FORM dbo.Table2 WHERE Parent_Id = @currentParent; in the loop and stop if no new parent is found.