Rekursive Abfrage für Unterkategorien

[Codewalker]

Hallo zusammen.

Ich verzweifele gerade an einer rekursiven Abfrage. Ich habe eine Tabelle mit Kategorien, die auch gleichzeitig eine Verschachtelung in Unterkategorien anbietet. Das ganze sieht dann etwa so aus:

SQL-Code:

CatID | Bezeichnung | OberCatID
---------------------------------
1     | Hauptkategorie | NULL
2     | Kat 1          | 1
3     | Kat 2          | 1
4     | Kat 3          | 2
...

Ich möchte nun in einer Abfrage die richtige Reihenfolge für eine Baumstruktur bekommen und zusätzlich eine Spalte mit der Hierarchieebene ermitteln. Das ganze sollte dann so aussehen;

SQL-Code:

CatID | Bezeichnung | OberCatID | Tiefe
----------------------------------------
1     | Hauptkategorie | NULL | 1
2     | Kat 1          | 1    | 1
4     | Kat 3          | 2    | 2
3     | Kat 2          | 1    | 1
...

Damit möchte in dann später eine Baumansicht generieren. Das Tabellendesign muss ich also so vorgegeben betrachten und ich möchte es gerne soweit wie möglich in SQL lösen. Das ganze war für mich als persönliche Klausurvorbereitung gedacht zum transitiven Abschluss. Mittlerweile glaube ich aber, dass man das anders lösen muss, da ich beim transitiven Abschluss ja auch alle indirekten Beziehungen bekomme.

Hat jemand eine Idee, wie man dieses Problem lösen könnte?

Viele Grüße

[mkinzler]

Solle der gabze Baum in einem Rutsch ausgelesen werden? Dann würde sich eine SP anbieten.

[Codewalker]

Der Baum ist eigentlich zweitrangig. Es geht mir in erster Linie darum, die Abfrage hinsichtlich der Sortierung und Ebenentiefe hinzubekommen. Daraus kann man ja später recht leicht einen Baum erzeugen.

[mkinzler]

Die Frage ist aber immer Ast für Ast oder alles auf einmal.

[Codewalker]

Es soll schon die ganze Tabelle auf einmal dabei rauskommen. (wenn ich dich jetzt richtig verstanden habe). Also eine Abfrage und aus der oben gezeigten Tabelle 1 wird die komplette Tabelle 2.

Edit: Vielleicht hilft ein wenig konkreteres Beispielmaterial

Code:

Tabelle in der Datenbank:

Vorgänger      ID      Bezeichnung
NULL               1      Literatur
1               2      Sachbücher
1               3      Belletristik
2               4      IT
2               5      Kochbücher
4               6      Programmierung
4               7      Datenbanken
5               8      Jamie Oliver
3               9      Krimi
3               10   Fantasy
3               11   Sci-Fi
11               12   Perry Rhodan


Zugehöriger Baum (nur für die Vorstellung):

Literatur
   Sachbücher
      IT
         Programmierung
         Datenbanken
      Kochbücher
         Jamie Oliver
   Belletristik
      Krimi
      Fantasy
      Sci-Fi
         Perry Rhodan
         
gewünschtes Ergebnis der Abfrage:

Vorgänger      ID      Bezeichnung            Tiefe
NULL               1      Literatur      0
1               2      Sachbücher      1
2               4      IT         2
4               6      Programmierung      3
4               7      Datenbanken      3
2               5      Kochbücher      2
5               8      Jamie Oliver      3
1               3      Belletristik      1
3               9      Krimi         2
3               10   Fantasy            2
3               11   Sci-Fi            2
11               12   Perry Rhodan         3

Ist leider etwas verrutscht, aber man kann hoffentlich erkennen, was zu welcher Spalte gehört.

[daddy]

Habe gerade mal anhand eines kleinen Beispiels einen möglichen Lösungsansatz ausprobiert, der genutzt werden kann, wenn die Tiefe der Ebenen begrenzt ist (in meinem Beispiel auf 5) und wenn die Zahl der Kategorien einer Ebene begrenzt ist (in meinem Beispiel auf 99.999). Vielleicht hilft Dir dieser Ansatz ja weiter. Die Syntax bezieht sich übrigens auf Firebird, kann in MySQL also abweichen.

Gruß Daddy


Tabelle erstellen:

SQL-Code:

Create Table Kat
(ID Integer Not Null,
 Bez VarChar (30) Not Null,
 OberID Integer,
 Primary Key (ID));

Tabelle füllen:
ID.......Bez...............OberID
---------------------------------
1........Kat 0.............Null
2........Kat 0-1...........1
3........Kat 0-2...........1
4........Kat 0-1-1.........2
5........Kat 0-1-2.........2
6........Kat 0-1-1-1.......4
7........Kat 0-1-1-2.......4

Select-Befehl:

SQL-Code:

Select
Case When K3.OberID is NULL then
Case When K2.OberID is NULL then
Case When K1.OberID is NULL then
Case When K0.OberID is NULL then 'A'||Cast(K0.ID + 100000 as VarChar(6))
else 'A'||Cast(K1.ID + 100000 as VarChar(6))||'B'||Cast(K0.ID + 100000 as Var-Char(6)) End
else 'A'||Cast(K2.ID + 100000 as VarChar(6))||'B'||Cast(K1.ID + 100000 as Var-Char(6))||'C'||Cast(K0.ID + 100000 as VarChar(6))  End
else 'A'||Cast(K3.ID + 100000 as VarChar(6))||'B'||Cast(K2.ID + 100000 as Var-Char(6))||'C'||Cast(K1.ID + 100000 as VarChar(6))||'D'||Cast(K0.ID + 100000 as Var-Char(6)) End
else 'A'||Cast(K4.ID + 100000 as VarChar(6))||'B'||Cast(K3.ID + 100000 as Var-Char(6))||'C'||Cast(K2.ID + 100000 as VarChar(6))||'D'||Cast(K1.ID + 100000 as Var-Char(6))||'E'||Cast(K0.ID + 100000 as VarChar(6)) End as Sortierung,
Case When K4.OberID is NULL then
Case When K3.OberID is NULL then
Case When K2.OberID is NULL then
Case When K1.OberID is NULL then
Case When K0.OberID is NULL then 1
else 2 End
else 3 End
else 4 End
else 5 End
else 6 End as Ebene,
K0.ID, K0.Bez
from Kat K0
left join Kat K1 on K0.OberID = K1.ID
left join Kat K2 on K1.OberID = K2.ID
left join Kat K3 on K2.OberID = K3.ID
left join Kat K4 on K3.OberID = K4.ID
order by 1

Ergebnis des Selects:
Sortierung.........................Ebene...ID..... .Bez
--------------------------------------------------------------
A100001............................1.......1...... .Kat 0
A100001B100002.....................2.......2...... .Kat 0-1
A100001B100002C100004..............3.......4...... .Kat 0-1-1
A100001B100002C100004D100006.......4.......6...... .Kat 0-1-1-1
A100001B100002C100004D100007.......4.......7...... .Kat 0-1-1-2
A100001B100002C100005..............3.......5...... .Kat 0-1-2
A100001B100003.....................2.......3...... .Kat 0-2

[busybyte]

SQL Nested

[ULIK]

Weil ich's grad reinbekommen hab: schau Dir mal SQL Antipatterns an. Dort aus dem Inhaltsverzeinis das Beispielskapitel 'Naive Trees'. Vieleicht hilft Dir das was.

Grüße,
Uli