Reverse Drill down for recursive entity

Hi,
I've a table category which is recursive on it
following is the sample
Category_IdCategory_NameParent_Category
1Entertainment0
2Food0
3Edible Oils2
4Toys1
5Ch. Free Oils.3
6Bicycles4
i know only 6 as category id, i need the lightweight query/sp which drills
up to the parent(untill the parent is 0) and select each category as row like.
6Bicycles
4Toys
1Entertainment
please help. If it is wrong newsgroup, please tell me the correct one.
Fahad Ashfaque
Fahad,
See if you can adapt this to your situation:
CREATE TABLE hierarchy (
parent VARCHAR(20)NULL,
child VARCHAR(20) NOT NULL
)
GO
INSERT hierarchy VALUES(NULL,'World')
INSERT hierarchy VALUES('World','Europe')
INSERT hierarchy VALUES('World','North America')
INSERT hierarchy VALUES('Europe','France')
INSERT hierarchy VALUES('France','Paris')
INSERT hierarchy VALUES('North America','United States')
INSERT hierarchy VALUES('North America','Canada')
INSERT hierarchy VALUES('United States','New York')
INSERT hierarchy VALUES('United States','Washington')
INSERT hierarchy VALUES('New York','New York City')
INSERT hierarchy VALUES('Washington','Redmond')
GO
CREATE FUNCTION PathUp(
@.parent VARCHAR(20)
) RETURNS @.t TABLE (
parent VARCHAR(20),
child VARCHAR(20)
) AS BEGIN
DECLARE @.child VARCHAR(20)
WHILE @.parent IS NOT NULL BEGIN
SET @.child = @.parent
SET @.parent = (
SELECT parent
FROM hierarchy
WHERE child = @.parent
)
INSERT INTO @.t VALUES (@.parent, @.child)
END
RETURN
END
GO
SELECT * from PathUp('New York City')
GO
Steve Kass
Drew University
Fahad Ashfaque wrote:

>Hi,
>I've a table category which is recursive on it
>following is the sample
>Category_IdCategory_NameParent_Category
>1Entertainment0
>2Food0
>3Edible Oils2
>4Toys1
>5Ch. Free Oils.3
>6Bicycles4
>
>i know only 6 as category id, i need the lightweight query/sp which drills
>up to the parent(untill the parent is 0) and select each category as row like.
>6Bicycles
>4Toys
>1Entertainment
>
>please help. If it is wrong newsgroup, please tell me the correct one.
>Fahad Ashfaque
>
|||Thanks Steve,
That works.
"Steve Kass" wrote:

> Fahad,
> See if you can adapt this to your situation:
> CREATE TABLE hierarchy (
> parent VARCHAR(20)NULL,
> child VARCHAR(20) NOT NULL
> )
> GO
> INSERT hierarchy VALUES(NULL,'World')
> INSERT hierarchy VALUES('World','Europe')
> INSERT hierarchy VALUES('World','North America')
> INSERT hierarchy VALUES('Europe','France')
> INSERT hierarchy VALUES('France','Paris')
> INSERT hierarchy VALUES('North America','United States')
> INSERT hierarchy VALUES('North America','Canada')
> INSERT hierarchy VALUES('United States','New York')
> INSERT hierarchy VALUES('United States','Washington')
> INSERT hierarchy VALUES('New York','New York City')
> INSERT hierarchy VALUES('Washington','Redmond')
> GO
> CREATE FUNCTION PathUp(
> @.parent VARCHAR(20)
> ) RETURNS @.t TABLE (
> parent VARCHAR(20),
> child VARCHAR(20)
> ) AS BEGIN
> DECLARE @.child VARCHAR(20)
> WHILE @.parent IS NOT NULL BEGIN
> SET @.child = @.parent
> SET @.parent = (
> SELECT parent
> FROM hierarchy
> WHERE child = @.parent
> )
> INSERT INTO @.t VALUES (@.parent, @.child)
> END
> RETURN
> END
> GO
> SELECT * from PathUp('New York City')
> GO
> Steve Kass
> Drew University
> Fahad Ashfaque wrote:
>
|||Thanks a lot - this helped me too!