Hierarchy Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Hierarchy Query

Author

Topic

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-23 : 02:44:32

Hi All,

I got a strange requirement related to hierarchy
-- Sampledata
DECLARE @T_Table TABLE (ChildId INT, ChildId2 INT)

INSERT INTO @T_Table
SELECT 9, 15 UNION ALL
SELECT 15, 16 UNION ALL
SELECT 33, 16 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 5, 4 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 33, 34

SELECT * FROM @T_Table

-- desired output for ClientId 9
ChildId ChildId2
9 15
15 16
33 16
33 34

---- for ClientId 1

ChildId ChildId2
1 2
3 4
5 4
4 2

i tried with this code but not getting desired results

; WITH A ( ChildId, ChildId2)
AS
(
SELECT ChildId, ChildId2
FROM @T_Table
WHERE ChildId = @ChildId

UNION ALL

SELECT SA.ChildId, SA.ChildId2
FROM A
INNER JOIN @T_Table SA ON (SA.ChildId = A.ChildId2)

UNION ALL

SELECT SA.ChildId2, SA.ChildId
FROM A
INNER JOIN @T_Table SA ON (SA.ChildId2 = A.ChildId2)
)

SELECT ChildId, ChildId2
FROM A

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 04:09:46

[code]SET NOCOUNT ON

DECLARE @Sample TABLE (ChildID1 INT, ChildID2 INT)

INSERT @Sample
SELECT 9, 15 UNION ALL
SELECT 15, 16 UNION ALL
SELECT 33, 16 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 5, 4 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 33, 34

DECLARE @StartingChild INT,
@Lvl INT

SELECT @StartingChild = 9,
@Lvl = 0

DECLARE @Stage TABLE (Lvl INT, ChildID1 INT, ChildID2 INT)

INSERT @Stage
(
Lvl,
ChildID1,
ChildID2
)
SELECT 0,
ChildID1,
ChildID2
FROM @Sample
WHERE @StartingChild IN (ChildID1, ChildID2)

WHILE @@ROWCOUNT > 0
BEGIN
SET @Lvl = @Lvl + 1

INSERT @Stage
(
Lvl,
ChildID1,
ChildID2
)
SELECT @Lvl,
s.ChildID1,
s.ChildID2
FROM @Sample AS s
INNER JOIN @Stage AS x ON x.Lvl = @Lvl - 1
WHERE (x.ChildID1 IN (s.ChildID1, s.ChildID2) OR x.ChildID2 IN (s.ChildID1, s.ChildID2))
AND NOT EXISTS (SELECT * FROM @Stage AS w WHERE w.ChildID1 = s.ChildID1 AND w.ChildID2 = s.ChildID2)
END

SELECT ChildID1,
ChildID2
FROM @Stage[/code]

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-23 : 04:54:50

Thanks PESO, i ll test with my actual data.
Can u tell me how to do this using CTE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:01:57

Only if you also have an identity column.
Otherwise the CTE will allow a circular reference search.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:02:33

Or you can place the suggested code in a table-valued function?

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-23 : 05:36:23

This will do it, I think...

; with
  a (x, y) as (select ChildId, ChildId2 from @T_Table union all select ChildId2, ChildId from @T_Table)
, b as (select * from a where x = @StartingChild
    union all select a.* from a inner join b on b.y = a.x and b.x <> a.y)
select x as ChildId, y as ChildId2 from b

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-23 : 05:38:20

If the order of the pairs is important, you can join it back to your original table...

; with
  a (x, y) as (select ChildId, ChildId2 from @T_Table union all select ChildId2, ChildId from @T_Table)
, b as (select * from a where x = @StartingChild
    union all select a.* from a inner join b on b.y = a.x and b.x <> a.y)
select a.* from @T_Table a inner join b on (a.ChildId = b.x and a.ChildId2 = b.y) or (a.ChildId = b.y and a.ChildId2 = b.x)

What about the order of the rows - is that important?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-23 : 05:42:55

Ryan, u r first query also works fine.
the order of the results is not important.

Thanks all of u for quick reply.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:44:11

Nice trick with two CTE's!

It's good to see you back in business

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-23 : 06:03:05

Thanks guys.

Peso - You work at such a rate (and high quality), I have to work hard to find useful things to do here!

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources