• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

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
0
Andy Green
Asked:
Andy Green
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Reza RadCommented:
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Reza RadCommented:
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
 
mabbj747Commented:
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now