SQAL question

  • Thread starter Thread starter Wernfried Schwenkner
  • Start date Start date
W

Wernfried Schwenkner

Sorry, but I didn't find a group for SQL questions. May be someone can
answer here.

In my app I want to reflect a hirarchy in a tree view. Therefore in the
database I have a table like this:

HirachyId (primary key)
ParentHirarchyId (key to reflect the arent entry)
CategoryId (a foreign key into a category table)

Content may be like the following (0 is root hirarchy)
1 0 10
2 0 11
3 1 16
4 1 8
5 3 27
6 3 28

0
+-- 1
| +-- 3
| | +-- 5
| | +-- 6
| |
| +-- 4
|
+-- 2


Now I want a SQL-select statement which "recursive" gives me all records
for a given node, i.e., when the node is "1" I want to get the records

1 0 10
3 1 16
4 1 8
5 3 27
6 3 28

Can this be done with a SQL-statement?
 
Sorry, but I didn't find a group for SQL questions. May be someone can
answer here.
Click to expand...

All the SQL gods hang out in microsoft.public.sqlserver.programming
--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"
 
Wernfried said:
Sorry, but I didn't find a group for SQL questions. May be someone can
answer here.

In my app I want to reflect a hirarchy in a tree view. Therefore in the
database I have a table like this:

HirachyId (primary key)
ParentHirarchyId (key to reflect the arent entry)
CategoryId (a foreign key into a category table)

Content may be like the following (0 is root hirarchy)
1 0 10
2 0 11
3 1 16
4 1 8
5 3 27
6 3 28

0
+-- 1
| +-- 3
| | +-- 5
| | +-- 6
| |
| +-- 4
|
+-- 2


Now I want a SQL-select statement which "recursive" gives me all records
for a given node, i.e., when the node is "1" I want to get the records

1 0 10
3 1 16
4 1 8
5 3 27
6 3 28

Can this be done with a SQL-statement?
Click to expand...

No, T-SQL does not support that. Oracle does though.
 
Wernfried Schwenkner said:
Sorry, but I didn't find a group for SQL questions. May be someone can
answer here.

In my app I want to reflect a hirarchy in a tree view. Therefore in the
database I have a table like this:

HirachyId (primary key)
ParentHirarchyId (key to reflect the arent entry)
CategoryId (a foreign key into a category table)

Content may be like the following (0 is root hirarchy)
1 0 10
2 0 11
3 1 16
4 1 8
5 3 27
6 3 28

0
+-- 1
| +-- 3
| | +-- 5
| | +-- 6
| |
| +-- 4
|
+-- 2


Now I want a SQL-select statement which "recursive" gives me all records
for a given node, i.e., when the node is "1" I want to get the records

1 0 10
3 1 16
4 1 8
5 3 27
6 3 28

Can this be done with a SQL-statement?
Click to expand...
Here is a functions that will walk the Parent chain, given the Child Key, it
will return the Child's key and level, and walk the chain up to the parent
returning each rows key and level. In my structure the root of the chain I
set the Table's PK and the Parent Key the same. If you set it to zero, then
the WHERE claus will need to be modified so that the ParentTblKey <> 0.

CREATE Function dbo.fnWalkParentChain
(
@ChildKey Int
)
RETURNS @ParentChain Table
(
ObjectKey Int,
[Level] Int
)
AS
BEGIN

DECLARE
@Level Int
Set @Level = 0

IF EXISTS (SELECT 1 FROM dbo.<RecursiveTable) p WHERE p.TblKey = @ChildKey)
BEGIN
INSERT INTO @ObjectChain
VALUES (@ChildKey, @Level)
END
ELSE
BEGIN
RETURN (0) -- No parents for this child
END

WHILE @@RowCount > 0
BEGIN
SET @Level = @Level + -1
INSERT INTO @ObjectChain
SELECT
p.ParentTblKey,
@Level
FROM dbo<RecursiveTable> p
JOIN @ObjectChain pc
ON pc.ObjectKey = p.TblKey
AND pc.[Level] = @Level +1
WHERE p.TblKey <> p.ParentTblKey -- <<-- modify if root parent
key is set to zero.
END

RETURN
END
 
Wernfried Schwenkner said:
Sorry, but I didn't find a group for SQL questions. May be someone can
answer here.

In my app I want to reflect a hirarchy in a tree view. Therefore in the
database I have a table like this:

HirachyId (primary key)
ParentHirarchyId (key to reflect the arent entry)
CategoryId (a foreign key into a category table)

Content may be like the following (0 is root hirarchy)
1 0 10
2 0 11
3 1 16
4 1 8
5 3 27
6 3 28

0
+-- 1
| +-- 3
| | +-- 5
| | +-- 6
| |
| +-- 4
|
+-- 2


Now I want a SQL-select statement which "recursive" gives me all records
for a given node, i.e., when the node is "1" I want to get the records

1 0 10
3 1 16
4 1 8
5 3 27
6 3 28

Can this be done with a SQL-statement?
Click to expand...
Probably should have provided this function also, this one when given a key
it will walk the child chain.
Declare @Key Int
SET @key = 27
For example Select * From dbo.fnWalkParentChain(@Key)

ALTER Function dbo.fnWalkParentChain
(
@ObjectKey Int
)
RETURNS @ObjectChain Table
(
TblKey Int,
[Level] Int
)
AS
/*
***
*** NOTE: The root row is identified by the TblKey (PK) and the ParentTblKey
*** being equal
***
*/
BEGIN

DECLARE
@Level Int
Set @Level = 0

IF EXISTS (SELECT 1 FROM dbo.LiveParty p WHERE p.TblKey = @ObjectKey)
BEGIN
INSERT INTO @ObjectChain
VALUES (@ObjectKey @Level)
END
ELSE
BEGIN
RETURN (0) -- No children found
END

WHILE @@RowCount > 0
BEGIN
SET @Level = @Level + 1
INSERT INTO @ObjectChain
SELECT
p.TblKey,
@Level
FROM dbo.LiveParty p
JOIN @ObjectChain pc
ON pc.TblKey = p.ParentTblKey
AND pc.[Level] = @Level -1
END

RETURN
END
 
Back
Top