Hierarchical Query - Return top level parent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Hierarchical Query - Return top level parent

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-31 : 22:54:38

Bob Lynn writes "Suppose you have the following sample table:


id   parent_id
--   ---------
5    null
10   5
15   5
20   5
25   10
30   10
35   25

In SQL Server 2000 is it possible to define a query that does the following:
"Give me the top level parent for any id."

So in this example, I want to construct a query where I feed it id=35 and it returns id=5 (since id=5 is at the top level).

I know in Oracle you can use the "connect by prior" statement to define hierarchical relationships ... does SQL Server have anything comparable???

Thanks for the help ..."

Lavos
Posting Yak Master

200 Posts

Posted - 2002-04-01 : 08:01:44

Sorry, but SQL Server doesn't have a a function similar to the connect by prior construct.

You'll have to use a loop to find the parent, but it's easy to fit it into a stored procedure (I just wrote one just the other day to find all the finished parts a component was used in.)

Ken Henderson's Guru's Guide has a good section on this, and Celko's SQL for Smarties has a few interesting approaches (though you'll have to adapt them to SQL Server)

My personal favorite (from Ken's book)

DECLARE @List TABLE (Lvl int, id int)
DECLARE @Lvl int

SELECT @Lvl=1

INSERT INTO @List
SELECT @Lvl, parent_id FROM YourTable WHERE id=@YourId

WHILE (@@ROWCOUNT > 0)
BEGIN
SELECT @Lvl = @Lvl+1
INSERT INTO @List
SELECT @Lvl, parent_id FROM YourTable INNER JOIN @List ON (YourTable.id=@List.id) WHERE Lvl=@Lvl-1
END

SELECT id FROM @List INNER JOIN YourTable ON (YourTable.id=@List.id)
WHERE parent_id IS NULL

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources