MSSQL Rekursionslevel bestimmen

[Norbert987]

Hallo Zusammen,

ich habe ein Problem mit SQL. Ich habe eine Tabelle "Genre" welche wie folgt aussieht:

Code:

ID   GenreBez       GenreSubID
-----------------------------------
1   Music           NULL
2   Rock            1
3   Pop             1
4   Electronica     1
5   Jazz            1
6   Hard Rock       2
7   Mainstream      2
8   Alternative     2
...

Die Tabelle ist nun folgendermaasen zu verstehen: 'Rock' und 'Pop' haben als SubGenre 'Music', 'Hard Rock' und 'Mainstream' haben als SubGenre 'Rock'. Ist es möglich, die Entfernungen rauszufinden? Also 'Music' <-> 'Rock' hat die Entfernung 1, 'Hard Rock' <-> 'Music' hat die Entfernung 2 etc.? Wie kann man das in eine neue Spalte machen, hat da jemand einen Tipp für mich?

Vielen Dank im Vorraus, Norbert

[generic]

Informiere dich mal über Nested Sets.

[Bummi]

Oder Du nimmst eine Spalte Level mit auf...

[rollstuhlfahrer]

Also per SQL sollte das nicht möglich sein. Was du alternativ machen kannst, ist das ganze in einen TreeView zu importieren und darüber die Abstände zu berechnen. Du musst dir das dann wie ein Baumdiagramm vorstellen.

Zu dem sollte: Ich denke mal nicht, dass es möglich ist, eine Rekursion mit unbestimmter Tiefe durchzuführen und dann die Rekursionstiefe zu ermitteln. Das ist selbst bei Windows-Programmen nur mit Mit-Zähl-Variablen möglich.

Bernhard

[Bummi]

@rollstuhlfahrer
man kann so eine Prozedur schon schreiben:

Code:

Create Procedure P_GetTreeDist(@ID1 int,@ID2 int) As
--201011 by Thomas Wassermann
Declare @count int
Declare @dist int

Select Cast(@ID1 as int) as ID,0 as Dist 
into #tmp1

Select Cast(@ID2 as int) as ID,0 as Dist 
into #tmp2

select @Count=0
Select @dist=1
While @Count<(Select Count(*) from #tmp1)
   begin
   Select @Count=(Select Count(*) from #tmp1)
   insert into #tmp1 Select Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from #tmp1) and refparent not in (Select ID from #tmp1)
   Select @dist = @dist + 1
   end

select @Count=0
Select @dist=1
While @Count<(Select Count(*) from #tmp2)
   begin
   Select @Count=(Select Count(*) from #tmp2)
   insert into #tmp2 Select Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from #tmp2) and refparent not in (Select ID from #tmp2)
   Select @dist = @dist + 1
   end

Select Min(#tmp1.Dist+#tmp2.Dist) as Distance from #tmp1 join #tmp2 on #tmp1.ID = #tmp2.ID

Drop Table #tmp1
Drop Table #tmp2

[Norbert987]

Vielen Dank für eure Anregungen. Die Funktion sieht ja wirklich passend aus Ich habe sie reinkompiliert und würde sie nun so nutzen:

Code:

select og.genreid as OGenreID,
       og.Bezeichnung as OGenreBezeichnung,
       ug.genreid as UGenreID,
       ug.Bezeichnung as UGenreBezeichnung,
       P_GetTreeDist(og.genreid, ug.genreid) as Entfernung
  from genre og,
       genre ug
 where Entfernung > 0

aber da mache ich wohl noch einen Fehler (''P_GetTreeDist' wird nicht als Name einer integrierten Funktion erkannt.'). Wie sieht solch ein Aufruf richtig aus?

[borwin]

Noch ein Gedanke.
Ab MSSQL 2005 geht auch folgendes. Sichwort Common Table Expressions (CTE)

Code:

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

Siehe hierzu bei MS CTE
oder auch CTE Beispiele
Hier ist auch ein Beispiel zur Ermittlung des Levels im SQL
Vorteil keine Funktionen und auch noch schneller.

SQL Syntax findet man auch bei Firebird


Gruß BORWIN

[Bummi]

@borwin

vielen Dank ....