Tree root
-
Hi
I want to get the top node in this tree from a known ID
CREATE TABLE ARBRE(ID int IDENTITY(1,1) NOT NULL,
NOM varchar(50) NULL,
ID_PERE int NULL
)
INSERT INTO ARBRE (NOM,ID_PERE)
SELECT 'HENRY1',NULL UNION ALL
SELECT 'HENRY7',NULL UNION ALL
SELECT 'HENRY8',2 UNION ALL
SELECT 'HENRY9',2 UNION ALL
SELECT 'HENRY17',4 UNION ALL
SELECT 'HENRY18',5 UNION ALL
SELECT 'HENRY20',5 UNION ALL
SELECT 'HENRY14',3
this table is a collection of person with their id,name and the id of their father
Example:
From "Henry20" we should find "Henry7" the first having ID_PERE Null and related to "Henry20"
Henry20 is related to henry17, henry17 is related to Henry9, Henry9 is related to Henry7, Henry7 have Null in the id_PERE
so we stop here and return it.
if it is possible an efficient solution with an unknown number of level otherwise we can presume that the max encapsulation is 3 (it means with "Henry20" we get "Henry9")
I hope this is clearer
Thanks, Moadh
-
At first I couldn't understand the "relationship" because it was in the identity column. :hehe:
Once I actually ran your create script it was very clear what you are trying to do. There is probably a more efficient way to do this with CTE but this will work fine if the table is not super huge.
declare @CurrentID int
Declare @ParentID int
set @CurrentID = 8 --here is the record we want to base node for
set @ParentID = -1 --seed this to an invalid number for the loop
while @ParentID is not null begin
select @ParentID = ID_PERE, @CurrentID = isnull(ID_PERE, @CurrentID) from arbre where ID = @CurrentID
end
select * from arbre where ID = @CurrentID
Hope that helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
DECLARE @NOM VARCHAR(50)SET @NOM='HENRY20';
WITH CTE AS (
SELECT ID,NOM,ID_PERE
FROM ARBRE
WHERE NOM=@NOM
UNION ALL
SELECT a.ID,a.NOM,a.ID_PERE
FROM CTE c
INNER JOIN ARBRE a ON a.ID=c.ID_PERE)
SELECT ID,NOM
FROM CTE
WHERE ID_PERE IS NULL
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks for answering.
domo arigato
Moadh.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply