Hierarchy Query Help! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Hierarchy Query Help!

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-08-04 : 07:58:00

Hello Friends,

I have a table that maintains heirarchy. When input is passed,its first child should be the output. Table data is as follows:
pageId-------parentId
46------------18
5-------------46
6-------------5
7-------------6
8-------------6
9-------------6
10------------6
11------------6
12------------6
13------------6
14------------6

For example: when the input is 46, the output should be 7. ( 46 has childern, its 1st child is 6. 6 has children, its first child is 7 )

I need something like recursive function.

Thanks a million.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 08:10:14

See Books Online for RECURSIVE CTE (Common Table Expression).

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 08:14:31

[code]DECLARE @Sample TABLE
(
PageID INT,
ParentID INT
)

INSERT @Sample
SELECT 46, 18 UNION ALL
SELECT 5, 46 UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 6 UNION ALL
SELECT 8, 6 UNION ALL
SELECT 9, 6 UNION ALL
SELECT 10, 6 UNION ALL
SELECT 11, 6 UNION ALL
SELECT 12, 6 UNION ALL
SELECT 13, 6 UNION ALL
SELECT 14, 6

DECLARE @NodeID INT

SET @NodeID = 18

;WITH Yak(PageID)
AS (
SELECT PageID
FROM @Sample
WHERE ParentID = @NodeID

UNION ALL

SELECT s.PageID
FROM Yak AS y
INNER JOIN @Sample AS s ON s.ParentID = y.PageID
)

SELECT PageID
FROM Yak[/code]

N 56°04'39.26"
E 12°55'05.63"

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-08-04 : 08:25:47

Thanks peso, i will check that and get back to YOU .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources