# [MySQL 5] rekursive Abfrage



## webbies (16. April 2008)

Hallo zusammen,

ich habe eine Tabelle mit folgendem Aufbau:

gID | name | parentID

Wenn parentID null ist, handelt es sich dabei um eine Hauptkategorie. Wenn parentID belegt ist, ist es eine Unterkategorie. Nun brauche ich eine Abfrage, die mir alle Unterkategorien einer bestimmten Kategorie liefert. D.h. wenn folgende Daten in der Datenbank wären:

1 | test 1 | null
2 | test 2 | 1
3 | test 3 | 1
4 | test 4 | 2

und ich frage "Gib mir mal ALLE Unterkategorien von test 1" Dann müsste als Ergebnis 2, 3 und 4 kommen, da 4 eine Unterkategorie von 2 ist und somit 1 untergeordnet ist. Hat jemand eine Idee, wie ich das rein mit sql hinbekomme? Geht das überhaupt?

Viele Grüße

webbies


----------



## dbwizard (16. April 2008)

webbies hat gesagt.:


> Hallo zusammen,
> 
> ich habe eine Tabelle mit folgendem Aufbau:
> 
> ...



Hallo,

ist es das, was du suchst ?


```
select parentid, group_concat(gid) from myTable
group by parentid;
```

Gruss


----------



## webbies (16. April 2008)

Hallo dbwizard,

ich habe den Befehl grade mal ausprobiert. Leider ist er nicht ganz so wie gewünscht. Dieser Befehl liefert mir alle Unterkategorien erster Ebene, in meinem Beispiel also 1, 2, 3 und dann als eigene Zeile das Ergebnis 2, 4. Ich bräuchte aber rekursiv alle Unterkategorien, also von 1 wie gesagt 2, 3, 4

Wie gesagt weiß ich gar nicht, ob das mit sql alleine überhaupt möglich ist.

Vielen dank und Grüße

webbies


----------



## olqs (16. April 2008)

Hmm ich hab mal etwas ausprobiert und bekomm schon alles zurück, allerdings in einzelnen Resultsets.


```
mysql> set @@max_sp_recursion_depth=10;
Query OK, 0 rows affected (0.00 sec)

mysql> call show_cat(1);
+-----+-------------+----------+
| gID | name        | parentID |
+-----+-------------+----------+
|   2 | Kategorie 2 |        1 | 
+-----+-------------+----------+
1 row in set (0.00 sec)

+-----+-------------+----------+
| gID | name        | parentID |
+-----+-------------+----------+
|   4 | Kategorie 4 |        2 | 
+-----+-------------+----------+
1 row in set (0.00 sec)

+-----+-------------+----------+
| gID | name        | parentID |
+-----+-------------+----------+
|   3 | Kategorie 3 |        1 | 
+-----+-------------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
```

Die Stored Procedure sieht so aus:

```
drop procedure show_cat;

DELIMITER $$

create procedure show_cat(IN cat_id int)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE var_gID INT;

  DECLARE cur1 CURSOR FOR Select gID from tab where parentID = cat_id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  
  REPEAT FETCH cur1 into var_gID;
   
    IF NOT done THEN
      select * from tab where gID = var_gID;
      call show_cat(var_gID);
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
END;

$$

DELIMITER ;
```


----------



## webbies (16. April 2008)

Hallo olqs,

vielen dank für dein Beispiel. Wenn ich die Prozedur aufrufe bekomme ich leider die Meldung

PROCEDURE show_cat can't return a result set in the given context

Kann man aus den mehreren Resultsets auch ein gesamtes machen?

Viele Grüße

webbies


----------



## olqs (16. April 2008)

Das kann natürlich daran liegen, dass deine Tabelle die du abfragen willst anders heisst.

Bei den 2 Select in der Procedure nach from einfach den Tabellennamen anpassen.

Wie/Ob man das in ein Resultset packen kann bin ich mir noch nicht sicher, aber ich bin am testen.


----------



## webbies (16. April 2008)

die beiden Tabellen habe ich umbenannt. Ist dafür evtl MySQL 5.1 notwendig? Ich habe nämlich 5.0.24. Meine Prozedur sieht wie folgt aus:


```
DROP procedure show_cat;

DELIMITER $$

CREATE procedure show_cat(IN cat_id int)
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE var_gID INT;
	
	DECLARE cur1 CURSOR FOR SELECT gID FROM category WHERE parentID = cat_id;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	OPEN cur1;
	
	REPEAT FETCH cur1 INTO var_gID;
	
	IF NOT done THEN
		SELECT * FROM category WHERE gID = var_gID;
		call show_cat(var_gID);
	END IF;
	
	UNTIL done END REPEAT;
	
	CLOSE cur1;
END;

$$

DELIMITER ;
```

Beim Anlegen sagt MySQL Aber auch

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

doch es wird eine Prozedur angelegt.

PS: kann man eine Abfrage wie diese Ausführen? SELECT * FROM tab WHERE id IN (show_cat(1));
Dann würde es nämlich ausreichen, wenn show_cat 2,3,4 als String liefert und keine komplettes Resultset oder?


----------



## olqs (16. April 2008)

Wie legst du denn die Prozedur an? Ich mach das über den Mysql CLI client.
Deine Version sollte da nichts ausmachen, ich benutz hier 5.0.54.

Das DELIMITER Kommando ist nur ein Befehl für den Client um die Strichpunkte innerhalb der Definiton der Prodezur nicht als Ende des Befehls zu interpretieren.

Hab das jetzt in einem Resultset gelöst:

```
DROP PROCEDURE IF EXISTS show_cat;

DELIMITER $$

CREATE PROCEDURE show_cat(IN cat_id INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE var_gID INT;
  DECLARE root_proc INT DEFAULT 0;
  DECLARE no_rows INT;

  DECLARE cur1 CURSOR FOR Select gID from tab where parentID = cat_id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  
  CREATE TABLE IF NOT EXISTS tmptbl (id INT PRIMARY KEY AUTO_INCREMENT, gID INT, name VARCHAR(255), parentID INT) ENGINE=MEMORY;

  SELECT COUNT(*) INTO no_rows FROM tmptbl;
  IF no_rows = 0 then
    SET root_proc = 1;
  END IF;

  REPEAT FETCH cur1 into var_gID;
   
    IF NOT done THEN
      INSERT INTO tmptbl (gID,name,parentID) SELECT * FROM tab WHERE gID = var_gID;
      CALL show_cat(var_gID);
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  
  IF root_proc = 1 THEN
    SELECT gID,name,parentID
    FROM tmptbl
    ORDER BY id;

    DROP TABLE tmptbl;
  END IF;
     
END;

$$

DELIMITER ;
```


----------



## webbies (16. April 2008)

ich mache das mit phpMyAdmin. In der Struktur der Tabelle sehe ich dann auch unten eine Routine mit dem Inhalt


```
DROP PROCEDURE `show_cat`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `show_cat`(IN cat_id int)
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE var_gID INT;
	DECLARE root_proc INT DEFAULT 0;
	DECLARE no_rows INT;
	
	DECLARE cur1 CURSOR FOR SELECT gID FROM category WHERE parentID = cat_id;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	OPEN cur1;
	
	CREATE TABLE IF NOT EXISTS tmptbl (id INT PRIMARY KEY AUTO_INCREMENT, gID INT, name VARCHAR(255), parentID INT) ENGINE=MEMORY;
	
	SELECT COUNT(*) INTO no_rows FROM tmptbl;
	IF no_rows = 0 then
		SET root_proc = 1;
	END IF;
	
	REPEAT FETCH cur1 INTO var_gID;
		IF NOT done THEN
			INSERT INTO tmptbl (gID,name,parentID) SELECT * FROM category WHERE gID = var_gID;
			CALL show_cat(var_gID);
		END IF;
	UNTIL done END REPEAT;
	
	CLOSE cur1;
	
	IF root_proc = 1 THEN
		SELECT gID, name, parentID FROM tmptbl ORDER BY id;
		
		DROP TABLE tmptbl;
	END IF;
END
```

Doch bei einem Aufruf kommt der bereits genannte Fehler

PROCEDURE show_cat can't return a result set in the given context

Kann ich das irgendwie debuggen?

Viele Grüße

webbies


----------



## olqs (16. April 2008)

OK die DELIMITER Angabe brauchst du bei phpMyAdmin nicht.
Die Prozedure wurde richtig angelegt.

Nur bietet phpMyAdmin für Stored Procedures leider keine gute Unterstützung.
Man muss in der Config von phpMyAdmin die extsension von mysql auf mysqli umstellen, damit die ohne Fehler ausgeführt werden kann.
Eine Ausgabe erhalte ich aber dann trotzdem noch nicht.

Ich würde jetzt mal behaupten, dass es am verwendeten DB Client liegt. Mit dem Konsolenclient klappts ohne Probleme.
Vielleicht hilft auch ein Update deiner phpMyAdmin Version.

Noch eine kleine Anmerkung: Falls es in der DB schon eine Tabelle tmptbl gibt, oder die aufgrund eines nichtdurchlaufens der Routine noch besteht, dann muss man die per Hand droppen damit die SP funktioniert. Das ist leider nicht so einfach zu umgehen


----------



## webbies (16. April 2008)

ja, die Abfrage gibt die richtige Datenbank an. Kann es vielleicht daran liegen, dass die Tabellen den Typ InnoDB haben? Das hatte ich nachträglich mal geändert um die Beziehungen abbilden zu können, zuvor war es MyISAM.


----------



## olqs (16. April 2008)

Hab grad im Moment meinen Beitrag oben editiert.  War da wohl zu langsam.


----------



## webbies (18. April 2008)

Hallo olqs,

sorry, dass ich erst jetzt wieder schreibe. Du hattest recht, über die Konsole geht es wunderbar. Ich danke dir vielmals für deine Mühe, damit hast du mir wirklich sehr geholfen!

Viele Grüße

webbies


----------

