Get Top most parent id

Hi

I have a recursive table that I use to populate a treeview control.

What I need is a routine that from any given tree node, will give me the ID of the first parent node in the tree. ie  I want the top node that has the parent ID = null, (as its the top)

The selected tree node can be anywhere down the tree hierarchy.

Andy
LVL 3
Andy GreenAsked:
Who is Participating?
 
mabbj747Connect With a Mentor Commented:
Try this

CREATE PROCEDURE [dbo].[uspGetParentNodeId]
   @Id   INT
AS
BEGIN
   SET NOCOUNT ON
   
   DECLARE @ParentId INT

   SELECT @ParentId = ParentId
   FROM tbYourTable
   WHERE Id = @Id
   
   IF @Id IS NULL
   BEGIN
         RETURN @Id
   END
   ELSE
   BEGIN
         EXECUTE uspGetParentNodeId @ParentId
   END
END
GO
0
 
Reza RadConsultant, TrainerCommented:
select top 1 * from table1 where parentID is null
0
 
Andy GreenAuthor Commented:
Not what I need, the ID of the clicked node in buried in the hierarchy, there are several top level nodes with Parent ID = null. This is a self referencing table, when you click  a node I need to get its parent, if that node is a child then get its parent etc all the way to the top, but..... It could be 1 hop to get to the top or 5. In each case I need the top level node ID.
 
Andy
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there is a iterative method:
http://support.microsoft.com/kb/248915

with sql 2005+, you can use the recursive query method:
http://www.mssqltips.com/tip.asp?tip=1520

all you need to "change" is in the final query to filter for parentid is null

0
 
Reza RadConsultant, TrainerCommented:
you can use cte for hirerchical data query like sample below:


USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO


msdn ref:
http://msdn.microsoft.com/en-us/library/ms175972.aspx


0
 
Andy GreenAuthor Commented:
Reza_Rad - this is what I'm doing to create the tree data.
Mabbj747 - Thanks ,  Routine always returns 0, debugging now.
Thanks
0
 
mabbj747Commented:
Sorry there is a mistake in the SP.

You need to change the IF condition to

 IF @ParentId IS NULL
   BEGIN
         RETURN @Id
   END

Sorry it was my mistake.
0
 
Andy GreenAuthor Commented:
Found it thanks. All working now.
Andy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.