SQL - Problem mit Hierarchien

[knoess]

Hallo zusammen

ich habe eine Tabelle "Person" die wie folgt aussieht:

PerId
PerName
PerVorname
PerParentId

Wie ihr seht hat jede Person eine Id und es kann eine ParentId hinterlegt werden. Die Tabelle "Person" verweist also mit dieser ParentId auf sich selber.

Bei einer SQL Abfrage auf diese Tabelle muss ich nun alle personen hierachisch herauslesen können. Von der Person an, die der User mir angibt. Der User gibt also im GUI die PersonId 4 an und das SQL-Query muss dann alle übergeordneten Personen der Person mit Id 4 herauslesen können. Und zwar muss das SQL-Script dies in einem ganz simplen Script können und zwar ohne Schleifen, spezielle Funktionen oder so (weil es auf mehreren RDBMS laufen muss).
Ich habe auch schon gegooglet etc. aber ich fand immer nur Lösungen mit irgend einer Schleife, das bringt mir aber leider nichts. Ich brauche ein rekursives Query aber ohne Schleife. Ich hoffe mal das geht irgendwie.. Kann mir einer von euch helfen?

 

[secret_3des]

Das geht nicht ohne Schleifen und rekursive Queries gibt es (meines Wissens) nicht.

 

[onyxus]

Moin,
Ob das ohne Schleife funktioniert, hängt in erster Linie davon ab, welche Implementierung von SQL du verwendest. MySQL und PostgeSQL können meines Wissens nach keine Rekursion, DB2 hingegen kann es

Also, welche Version nutzt Du?

 

[Relict]

Moin,
Ob das ohne Schleife funktioniert, hängt in erster Linie davon ab, welche Implementierung von SQL du verwendest. MySQL und PostgeSQL können meines Wissens nach keine Rekursion, DB2 hingegen kann es

Also, welche Version nutzt Du?

Rekursiv geht in MySQL zumidest nicht wirklich.
Aber es gibt eine Lösung ohne Schleifen, die allerdings erstmal etwas Grundverständnis vorraussetzt. Und natürlich eine etwas andere Organisation der Datensätze.
Google mal nach "Nested Set" oder hier

 

[knoess]

Die Lösung mit Nested Set sieht schon mal sehr gut aus. Vielen Dank! Da mein Query auf mehreren RDBMS lauffähig sein muss (unter anderem MS SQL, Oracle, Db2 etc.) habe ich dieses Problem, dass ich keine speziellen SQL Befehle wie eben die Schleife oder so verwenden kann.

Vielen Dank für euere Hilfe!

 

[olampl]

MSSql hat Stored Procedure, Oracle hat PL/SQL die sehr wohl schleifen kennen.

Zu Db2 kann ich nix sagen, damit habe ich noch nie gearbeitet, bin mir aber sicher das es dort auch ne skriptsprache gibt, die schleifen kann.

 

[onyxus]

Die Lösung mit Nested Set sieht schon mal sehr gut aus. Vielen Dank! Da mein Query auf mehreren RDBMS lauffähig sein muss (unter anderem MS SQL, Oracle, Db2 etc.) habe ich dieses Problem, dass ich keine speziellen SQL Befehle wie eben die Schleife oder so verwenden kann.

Vielen Dank für euere Hilfe!

Moin, meines Wissens würde SQL nach Standard das schon unterstützen, nur ist das halt in den frei verfügbaren Versionen (noch) nicht implementiert.

 

[looploop]

hallo,

ich bin selber laie. daher meine frage:
was spricht gegen das "anzahl der hirarchie stufen fache" joinen der tabelle?

normalerweise sollte doch die maximale anzahl der hierarchiestufen vorher bekannt sein?

wenn ich zum beispiel
mitarbeiter, teamleiter, gruppenleiter, abteilungsleiter, geschäftsführer
hätte, würde ich 5 hierarchiestufen haben.

folgendes query sollte überall laufen (bis auf die GUI Eingabe)

select *
from person p1, person p2, person p3, person p4, person p5
where p1.PerParentId = p2.PerId
and p2.PerParentId = p3.PerId
and p3.PerParentId = p4.PerId
and p4.PerParentId = p5.PerId
and p1.PerID = '&GUIEingabe.'

Ich nehme auch an, daß in dem Unternehmen nicht millionen von personen arbeiten, so dass es noch performant sein sollte, vor allem wenn man es entsprechend indeziert.

 

[Cobinja]

Die Anzahl von Hierarchiestufen ist nicht zwingend vorher bekannt.

Bei deinem Beispiel wäre sie zwar durch die Personalstruktur vorgegeben.
Was willst du aber machen, wenn das Programm in mehreren Firmen laufen soll? Die haben vielleicht (oder eher wahrscheinlich) verschiedene Strukturen. Trotzdem muss der Code laufen. Man will ja nicht für jeden Kunden die Baumstruktur neu implementieren.

Und da der Code auf verschiedenen RDBMSen laufen soll, bleiben wohl oder übel nur Nested Sets übrig.

BTW.: PostgreSQL unterstützt sehr wohl inzwischen Rekursionen. Dort habe ich mir mal eine kleine Stored Function geschrieben, um einen Baum auszulesen:

CREATE OR REPLACE FUNCTION get_region_sub_tree_rows(start_key bigint)
  RETURNS SETOF region_tree AS
$BODY$
DECLARE
  region_count           integer;
  current_region_ref     refcursor;
  current_region         record;
  sub_region_ref         refcursor;
  sub_region_keys        int8[];
  sub_region_index       int8;
  sub_region_keys_length int8;
  sub_regions            refcursor;
  sub_region             record;
BEGIN
  SELECT count(region_key) INTO region_count
    FROM region_tree
   WHERE region_key = start_key;
  
  IF region_count = 0 THEN
    RETURN;
  END IF;
  
  OPEN current_region_ref FOR EXECUTE
    'SELECT region_key, parent_key, name, created_date, created_by, modified_date, modified_by\n' ||
    '  FROM region_tree\n' ||
    ' WHERE region_key = ' || "start_key";
  
  FETCH current_region_ref INTO current_region;
  RETURN NEXT current_region;
  CLOSE current_region_ref;
  
  sub_region_keys := NULL;
  
  OPEN sub_region_ref FOR EXECUTE
    'SELECT region_key\n' ||
    '  FROM region_tree\n' ||
    ' WHERE parent_key = ' || current_region.region_key;
    
  LOOP
    FETCH sub_region_ref INTO sub_region;
    EXIT WHEN NOT FOUND;
    IF sub_region_keys IS NULL THEN
      sub_region_keys := ARRAY[sub_region.region_key];
    ELSE
      sub_region_keys := array_append(sub_region_keys, sub_region.region_key);
    END IF;
  END LOOP;
  CLOSE sub_region_ref;
  
  IF sub_region_keys IS NULL THEN
    sub_region_keys_length := 0;
  ELSE
    sub_region_keys_length := array_upper(sub_region_keys, 1);
  END IF;   
  IF sub_region_keys_length > 0 THEN
    FOR sub_region_index IN 1..sub_region_keys_length LOOP
      OPEN sub_regions FOR
            SELECT *
              FROM get_region_sub_tree_rows(sub_region_keys[sub_region_index]);
      LOOP
        FETCH sub_regions INTO sub_region;
        EXIT WHEN NOT FOUND;
        RETURN NEXT sub_region;
      END LOOP; -- fetch sub_regions
    CLOSE sub_regions;
    END LOOP; -- for sub_region_index
  END IF; 
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Ausgelesen wird dann mit

SELECT *
  FROM get_region_sub_tree_rows(2);

Funktioniert allerdings wegen dem Rückgabetyp nur mit einer Tabelle, für andere Tabellen muss die function entsprechend angepasst werden

 

[Admiralz]

Nested Sets in MySql