Verzwicktes PRoblem mit Select

[Kealsera]

MySQL-Client-Version: 5.1.41
phpMyAdmin Versionsinformationen: 3.2.4

Grüße,

ich bin gerade dabei SQL zu lernen und habe mir dafür eine Beispieldatenbank gebastelt.

z.Z. bin ich dabei, mir eingehendes Verständnis zum Select Befehl zu schaffen und habe dazu auch gleich eine Frage:

Ich tätige z.Z. folgende Abfrage:

SELECT LEHRERNAMEN.Nachname,LEHRERNAMEN.Vorname,Name FROM  
(SELECT DISTINCT Name,FachID FROM fach INNER JOIN lehrerfach USING (FachID)) AS FACH,  
(SELECT FachID,LehrerID from lehrerfach INNER JOIN lehrer USING (LehrerID)) AS LF,  
(SELECT Nachname,Vorname,AngestellterID FROM person INNER JOIN angestellter USING(PersonID)) AS LEHRERNAMEN,  
(SELECT AngestellterID,LehrerID FROM lehrer INNER JOIN angestellter USING(AngestellterID)) AS LEHRERID  
WHERE LEHRERNAMEN.AngestellterID = LEHRERID.AngestellterID AND LF.LehrerID = LEHRERID.LehrerID AND LF.FachID = FACH.FachID

  
Das Ergebnis der Abfrage lautet:  
  
Nachname Vorname Name  
Lehrer 	Klaus 	Mathematik  
Lehrer 	Klaus 	Deutsch  
Lehrer 	Klaus 	Englisch  
Lehrer 	Mathilde Erdkunde  
Lehrer 	Mathilde Geschichte  
Lehrer 	Georg 	Mathematik

Nun liegt der nächste Schritt nahe - Ich hätte gerne Pro Nachname Vorname Kombination alle zugehörigen Fächer in einer Zeile, und nicht pro Zeile einen Lehrer und ein Fach.

Nur komm ich z.Z. nicht dahinter, wie ich das bewerkstelligen könnte.
Hättet ihr eine Idee? :o

Grüße

[Bobby]

Moin

Es gibt keine "mehrdimensionalen Arrays" als Ergebnis eine SQL-Abfrage. Das ist immer eindimensional.

Ich würde dir empfehlen dich mit "Gruppenwechseln" zu beschäftigen.

Gruß Bobby

[Flanna]

Moin

wie Bobby schon sagt ist das was Du willst per SQL kaum lösbar.
Muss es aber auch nicht. Genau genommen ist das Datenaufbereitung und somit Aufgabe des Middle-Layers, also PHP oder ähnliches.

1. Data-Layer (Datenbank):  Daten zur Verfügung Stellen
2. Middle-Layer (Script-Sprache): Daten aufbereiten
3. Display-Layer (HTML): Anzeige der Daten formatieren

mfg
Flanna

[Alex]

Hi,

1. Data-Layer (Datenbank):  Daten zur Verfügung Stellen
2. Middle-Layer (Script-Sprache): Daten aufbereiten
3. Display-Layer (HTML): Anzeige der Daten formatieren

Interessant - man kann es sich auch schwerer machen als es ist. Vielleich tsolltest du auch mal meinen Tipp beherzigen.

Gruß
Alex

[Flanna]

Stichwort: Portabilität.

Das hat nichts mit schwerer zu tun, sondern damit das meine SQL Statements so einfach wie möglich bleiben. Ich hab  mich einmal in ner Firma durch gut ne halbe Mio. lines of code gehen dürfen und schauen ob irgendwo SQL Code steht der für Oracle funktioniert, in MS SQL (wohin umgestellt werden sollte) aber nicht.
Seitdem halt ich mich strikt an die 3 Layer und deren Aufgaben wie ich es auch in der Ausbildung gelernt hab.

Dazu kommt, das viele Programmierer nur Basis-Kenntnisse in SQL haben und bei solchen Konstrukten wie Deinem schon nurnoch Bahnhof verstehen. Das es auch mit Group By und Concat geht war mir durchaus klar. Aber nicht alles was geht ist gleichzeitig auch sinnvoll.

Das in der Praxis viele aus Bequemlichkeit anders arbeiten steht auf nem anderen Blatt Papier :)

[Bobby]

Moin

1. Data-Layer (Datenbank):  Daten zur Verfügung Stellen
2. Middle-Layer (Script-Sprache): Daten aufbereiten
3. Display-Layer (HTML): Anzeige der Daten formatieren

Das wird bei großen Datenmengen schnell zum Speicherüberlauf führen. Das weiß ich aus Erfahrung. Deswegen nutze ich nur die vom SQL zurückgegebenen Datensätze direkt und arbeite mit Gruppenwechseln. Über ordentliche SORT Klausel kann man das auch sehr gut bewerkstelligen.

Gruß Bobby

[Flanna]

Das wird bei großen Datenmengen schnell zum Speicherüberlauf führen. Das »» weiß ich aus Erfahrung. Deswegen nutze ich nur die vom SQL zurückgegebenen »» Datensätze direkt und arbeite mit Gruppenwechseln. Über ordentliche SORT »» Klausel kann man das auch sehr gut bewerkstelligen.

Ich bin mir noch nicht ganz im klaren darüber was Du mit Gruppenwechsel meinst.

Was den Speicherüberlauf betrifft: Jein. Grundsätzlich stimmt es natürlich. Allerdings kann PHP durchaus einiges verarbeiten und das sogar relativ permanent (wenn man sich weitgehend an natives php hält).
Auf jeden Fall mehr Daten als ein Benutzer jemals auf einmal sehen will bzw. im Falle von ner Webseite, der Browser noch ordentlich darstellen kann.

Das Problem des Speicherüberlaufs kommt insofern eigentlich nur dann zum tragen wenn man keinen Zugriff auf die memory_limit Einstellung der Php-ini hat oder der Server nicht genug Arbeitsspeicher hat. mit 256MB geht dann aber auch schon so ziemlich alles. Da knallts dann eher schon wegen Timeout^^

In der Praxis hab ich aber noch nie mehr als 80MB verbraucht. Und das war auch schon nur der Fall als aus flachen Daten eine Baumstruktur gebaut werden musste und das mit etwas über 20.000 Ästen die jeweils aus 10 Datenfeldern gestammt haben die teilweise noch berechnet werden mussten. Kranker Mist :D

Trotzdem, das mit dem Gruppenwechsel klingt interessant. Werd mal sehen ob ich dazu was finde. Man lernt eben doch nie aus^^

mfg
Flanna

[Bobby]

Moin

Ich bin mir noch nicht ganz im klaren darüber was Du mit Gruppenwechsel meinst.

Das ist zur Verarbeitung von Hirachien

z.B. folgende DAtenrückgabestruktur:

Gruppe 1 | Name 1
Gruppe 1 | Name 2
Gruppe 1 | Name 3
Gruppe 2 | Name 4
Gruppe 2 | Name 5

Um Nun strukturiert ausgeben zu können, Muss bei der Verarbeitung ein Gruppenwechsel vollzogen werden.

Es soll nun ausgegebenen werden. (Als Beispiel)

Gruppe 1
 - Name 1
 - Name 2
 - Name 3
Gruppe 2
 - Name 4
 - Name 5

Das heißt für jede HAuptebene (Gruppe) werden alle DAtensätze ausgegeben und entsprechend die Gruppe jeweils gewechselt. Du würdest das erst umkopieren z.B. in ein mehrdimensionales Array.

Als gutes Beispiel ist hier mal ne PDF für java zu finden. Das lässt sich aber 1:1 übertragen.

Gruß Bobby

[Flanna]

Ah ok, da hatte ich was falsch verstanden. Ich dachte das wäre irgendwas Datenbankseitiges a la Nested Sets oder so.

Die Vorgehensweise ist natürlich die logischste und sicherlich zu präferieren.
Da aber aus der inititalen Frage nicht hervorging ob das zwingend so sein soll das die Fächer in einer Zeile stehen oder nicht (was theoretisch ja durchaus auch Anforderung in ner Buisnessapplikation sein kann, beispielsweise für nen CSV-Export) bin ich davon ausgegangen das es zwingend so sein soll.

Trotzdem Danke für die Aufklärung :) Unter der Namen Gruppenwechsel kannte ich das tatsächlich nicht.

Moin

Ich bin mir noch nicht ganz im klaren darüber was Du mit Gruppenwechsel meinst.

Das ist zur Verarbeitung von Hirachien

z.B. folgende DAtenrückgabestruktur:

Gruppe 1 | Name 1
Gruppe 1 | Name 2
Gruppe 1 | Name 3
Gruppe 2 | Name 4
Gruppe 2 | Name 5

Um Nun strukturiert ausgeben zu können, Muss bei der Verarbeitung ein Gruppenwechsel vollzogen werden.

Es soll nun ausgegebenen werden. (Als Beispiel)

Gruppe 1

• Name 1
• Name 2
• Name 3
  Gruppe 2
• Name 4
• Name 5

Das heißt für jede HAuptebene (Gruppe) werden alle DAtensätze ausgegeben und entsprechend die Gruppe jeweils gewechselt. Du würdest das erst umkopieren z.B. in ein mehrdimensionales Array.

Als gutes Beispiel ist hier mal ne PDF für java zu finden. Das lässt sich aber 1:1 übertragen.

Gruß Bobby

[Bobby]

Moin

Die Vorgehensweise ist natürlich die logischste und sicherlich zu präferieren.
Da aber aus der inititalen Frage nicht hervorging ob das zwingend so sein soll das die Fächer in einer Zeile stehen oder nicht (was theoretisch ja durchaus auch Anforderung in ner Buisnessapplikation sein kann, beispielsweise für nen CSV-Export) bin ich davon ausgegangen das es zwingend so sein soll.

Na das ist doch aber von der Ausgabe abhängig. Die Frage vom OP war doch wie er es bewerkstelligen kann.

Ich bin davon ausgegangen, das er das direkte Ergebnis der SQL-Abfrage meinte.

Was ich ausdrücken will ist, das das Ergebnis:

Nachname Vorname Name
Lehrer         Klaus         Mathematik
Lehrer         Klaus         Deutsch
Lehrer         Klaus         Englisch
Lehrer         Mathilde Erdkunde
Lehrer         Mathilde Geschichte
Lehrer         Georg         Mathematik

so korrekt ist und durch die Weiterverarbeitung und Verwendung von Gruppenwechseln entsprechend ausgegeben werden kann, ohne die Daten nochmals behandeln und umspeichern zu müssen.

Trotzdem Danke für die Aufklärung :) Unter der Namen Gruppenwechsel kannte ich das tatsächlich nicht.

Das ist der Fachbegriff dazu... ;)

Gruß Bobby

[Kealsera]

Danke für die vielen Antworten ;)

Ich persönlich ging auch davon aus, dass es wohl am meisten Sinn macht die Daten im Business-Layer aufzubereiten - allerdings hat es mich interessiert, ob das SQL-Seitig nicht effektiver aufzubreiten ist.

Der Datenbank liegt übrigens eine Oberfläche auf C# Basis zu Grunde, da ihr öfter PhP eingestreut habt ;)

Abschließend nochmals danke, ich werd mich dann mal einlesen :)

[Alex]

HAllo,

Nun liegt der nächste Schritt nahe - Ich hätte gerne Pro Nachname Vorname Kombination alle zugehörigen Fächer in einer Zeile, und nicht pro Zeile einen Lehrer und ein Fach.

Nur komm ich z.Z. nicht dahinter, wie ich das bewerkstelligen könnte.
Hättet ihr eine Idee? :o

Um eine Zeile pro Person zu bekommen musst du erstmal nach z.B. der Angestellten-ID des Lehrers gruppieren. (GROUP BY)

Einen String, zusammengesetzt aus mehreren Werten kannst du mit CONCAT bilden.

Im Zusammenhang mit der Gruppierung brauchst du daher GROUP_CONCAT.

Gruß
Alex

[Bobby]

Moin

HAllo,

Nun liegt der nächste Schritt nahe - Ich hätte gerne Pro Nachname Vorname Kombination alle zugehörigen Fächer in einer Zeile, und nicht pro Zeile einen Lehrer und ein Fach.

Nur komm ich z.Z. nicht dahinter, wie ich das bewerkstelligen könnte.
Hättet ihr eine Idee? :o

Um eine Zeile pro Person zu bekommen musst du erstmal nach z.B. der Angestellten-ID des Lehrers gruppieren. (GROUP BY)

Einen String, zusammengesetzt aus mehreren Werten kannst du mit CONCAT bilden.

Im Zusammenhang mit der Gruppierung brauchst du daher GROUP_CONCAT.

Dann stünden aber die Fächer nicht mehr einzeln zur Verfügung. Somit kann z.B. nachträglich nicht sortiert werden. Deswegen ist ein Gruppenwechsel (oder ein middleArray) besser. Es stehen noch alle Daten einzeln zur Verfügung.

Gruß Bobby

[Vinzenz Mai]

Hallo,

MySQL-Client-Version: 5.1.41
phpMyAdmin Versionsinformationen: 3.2.4

nach diesen Angaben darfst Du Dich nicht über Antworten wundern, die PHP als Umgebung annehmen bzw. Anwendungscode aus der PHP-Welt. Die MySQL-Client-Version ist übrigens nicht so wichtig wie die MySQL-Server-Version :-)

ich bin gerade dabei SQL zu lernen und habe mir dafür eine Beispieldatenbank gebastelt.

Das halte ich für eine gute Idee, eine noch bessere wäre es gewesen, wenn Du die paar Beispieldaten mitgepostet hättest.

z.Z. bin ich dabei, mir eingehendes Verständnis zum Select Befehl zu schaffen und habe dazu auch gleich eine Frage:

das ist auch eine gute Idee, allerdings solltest Du Dir grundsätzlich bessere SQL-Grundlagen verschaffen, bevor Du - meiner Meinung nach seltsamen - Code wie den folgenden schreibst:

Ich tätige z.Z. folgende Abfrage:

SELECT LEHRERNAMEN.Nachname,LEHRERNAMEN.Vorname,Name FROM

(SELECT DISTINCT Name,FachID FROM fach INNER JOIN lehrerfach USING (FachID)) AS FACH,
(SELECT FachID,LehrerID from lehrerfach INNER JOIN lehrer USING (LehrerID)) AS LF,
(SELECT Nachname,Vorname,AngestellterID FROM person INNER JOIN angestellter USING(PersonID)) AS LEHRERNAMEN,
(SELECT AngestellterID,LehrerID FROM lehrer INNER JOIN angestellter USING(AngestellterID)) AS LEHRERID
WHERE LEHRERNAMEN.AngestellterID = LEHRERID.AngestellterID AND LF.LehrerID = LEHRERID.LehrerID AND LF.FachID = FACH.FachID

  
Du vermischst hier implizite (WHERE-Klausel) und explizite Joins mit der recht neuen USING-Klausel (SQL:2003), die noch besser unterstützt sein könnte. Willst Du Dir vernünftige Grundlagen zu Joins aneignen, so empfehle ich Dir zwei Artikel in SELFHTML aktuell bzw. dem SELFHTML-Wiki, auch wenn diese kein Wort über die USING-Klausel verlieren:  
  
 - [Einführung in Joins](http://aktuell.de.selfhtml.org/artikel/datenbanken/joins/)  
 - [Fortgeschrittene Jointechniken](http://wiki.selfhtml.org/wiki/Artikel:DBMS_und_SQL/Fortgeschrittene_Jointechniken)  
  
Ich persönlich würde hier kein einziges Subselect verwenden, keinen impliziten Join, sondern hübsch normale INNER JOINs mit ON-Klausel :-)  
  
Warum verwendest Du bei Deinem JOIN der Tabellen "lehrerfach" und "lehrer" ein DISTINCT?  
  
Du solltest statt dessen per UNIQUE-Index dafür sorgen, dass die gleiche Kombination von Lehrer und Fach nur ein einziges Mal vorkommt. Sie sollte angeben, welche Fächer ein Lehrer unterrichten kann.  
  
Zum Stil: ich nähme keine Großschreibung für Aliasnamen, Großschreibung behalte ich mir - wie sehr weit üblich - für SQL-Schlüsselwörter und -Funktionen vor.  
  

> Das Ergebnis der Abfrage lautet:  
  

> Nachname  Vorname   Name  
> ------------------------------  
> Lehrer    Klaus     Mathematik  
> Lehrer    Klaus     Deutsch  
> Lehrer    Klaus     Englisch  
> Lehrer    Mathilde  Erdkunde  
> Lehrer    Mathilde  Geschichte  
> Lehrer    Georg     Mathematik  
  

> Nun liegt der nächste Schritt nahe - Ich hätte gerne Pro Nachname Vorname Kombination alle zugehörigen Fächer in einer Zeile, und nicht pro Zeile einen Lehrer und ein Fach.  
  
Wie die [bei weitem beste Antwort](https://forum.selfhtml.org/?t=202857&m=1370743), die von Alex, es Dir sagte, ist das Problem alles andere als verzwickt, es ist simpel: einfach eine GROUP-BY-Klausel mit der entsprechenden Aggregatsfunktion, fertig.  
  
Ich käme somit zu  
  
~~~sql
SELECT  
    p.Nachname,  
    p.Vorname,  
    GROUP_CONCAT(f.Name SEPARATOR ', ')  
FROM  
    fach f  
INNER JOIN  
    lehrerfach lf  
ON  
    f.FachID = lf.FachID  
INNER JOIN  
    angestellter a  
ON  
    lf.LehrerID = a.AngestellterID  
INNER JOIN  
    person p  
ON  
    a.PersonID = p.PersonID  
GROUP BY  
    p.Nachname,  
    p.Vorname  

und stelle dabei fest, dass ich für diese Abfrage die Lehrertabelle überhaupt gar nicht benötige, weil sie für das Herausfinden von Vornamen, Nachnamen und der Liste der möglichen Unterrichtsfächer aller Lehrer völlig irrelevant ist. Das geht in Deiner unnötig komplexen Schreibweise einfach unter.

Meiner Meinung nach ist plattformübergreifendes SQL kein besonders wichtiges Ziel. Will man's plattformübergreifend haben, so sollte man einen entsprechenden Abstraktionslayer nutzen und nicht das SQL auf den kleinsten gemeinsamen Nenner reduzieren, der verdammt klein ausfällt.

Sobald Du die eine oder andere Funktion nutzen willst, sobald Du Datumsliterale verwendest, sobald Du so etwas nettes wie LIMIT ... OFFSET haben willst (siehe zum Beispiel diesen Archivthread), überschreitest Du die Plattformunabhängigkeit. Also mach' Dir dazu nicht allzu viel Gedanken außer dem, dass der SQL-Code, den Du gerade schreibst, genau auf dem DBMS das von Dir gewünschte Ergebnis liefert, das Du gerade nutzt - und anderswo Syntaxfehler liefern kann. Das ist in der Welt der relationalen Datenbanksysteme leider Gottes normal.

Nochmals: es geht nicht um Performance, es geht nicht um irgendwelche Applikationen, es geht um SQL. Konzentriere Dich auf SQL und lass solche Sachen mit API-Code wie einem Gruppenwechsel außen vor. Wenn mir SQL genau das liefert, was ich haben will, schreibe ich einfachen SQL-Code statt unnötigem API-Code (solange nicht klare Performancegründe für die API sprechen) :-)

Wenn ich SQL lernen will, schreibe ich SQL-Code von Hand, in einem entsprechenden Client wie zum Beispiel der MySQL-Workbench. Die Darstellung ist mir dabei genauso gleichgültig wie die Performance - es sei denn, ich will etwas über die Performance unterschiedlicher Herangehensweisen an das gleiche Problem lernen.

Freundliche Grüße

Vinzenz