Ankündigung

Einklappen
Keine Ankündigung bisher.

[Erledigt] kompliziertere SQL Abfrage / mehrere Joins

Einklappen

Neue Werbung 2019

Einklappen
X
Einklappen
  • Seite von 1
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge
Vorherige Weiter
  • #1

    [Erledigt] kompliziertere SQL Abfrage / mehrere Joins

    Hallo zusammen,

    bin in PHP und MySQL eigentlich ziemlich fit, komme aber bei folgender Datenbankstruktur gedanklich nicht weiter:

    Inhaltlich geht es bei der Datenbank um die Organisation von Nachhilfe - Verknüpfungen. Alle Projektteilnehmer stehen mit Namen etc. in der Tabelle "people". Zusätzlich möchte ich aber in der Lage sein, den Personen in "people" individuell Eigenschaften zuzuordnen. Das kann zum Beispiel die Klasse oder Schule oder aber auch eine Kontonummer sein. Dazu soll ein Eintrag in "people_attributes" existieren, bei dem "content" z.B. "XYZ Schule" ist und das Feld "attributes" eine Zahl enthält, die diese Eigenschaft als die Schule-Eigenschaft identifiziert (über "storage_attributes").

    Weiterhin können jeder Person aus "people" über die Tabelle "subjectlinks" Fächer zugeordnet werden. Dabei wird über das Feld "sid" eine Zahl angegeben, die das Fach angibt und über "teach" bzw. "learn" wird festgelegt, ob diese Person das Fach unterrichtet oder lernen möchte. Eine Person kann merhere Fächer lernen / unterrichten aber auch manche Fächer lernen und andere unterrichten.


    Tabelle: people
    pid (INT, Primary, AI)
    firstname (VARCHAR)
    surname (VARCHAR)
    usw.

    Tabelle: people_attributes
    paid (INT, Primary, AI)
    pid (INT, ForeignKey zu people.pid)
    attribute (INT, ForeignKey zu storage_attributes.aid)
    content (VARCHAR, z.B. "XYZ Schule" oder "6" oder "598732354" etc.)

    Tabelle: storage_attributes
    aid (INT, Primary, AI)
    name (VARCHAR ----> z.B. "Schule" oder "Jahrgangsstufe" oder "Kontonummer")

    Tabelle: subjectlinks
    slid (INT, Primary, AI)
    pid (INT, ForeignKey zu people.pid)
    sid (INT, ForeignKey zu storage_subjects.sid)
    teach (BOOLEAN)
    learn (BOOLEAN)

    Tabelle: storage_subjects
    sid (INT, Primary, AI)
    name (VARCHAR -----> z.B. "Mathematik", "Deutsch", "Englisch", etc.)

    Nun zum Ziel der gesuchten Abfrage: Ich brauche eine Abfrage, die mir beispielsweise alle Personen anzeigen, die ein bestimmtes Fach unterrichten (sagen wir z.B. "Mathematik"). Die Abfrage soll folgende (sichtbare) Felder enthalten:

    people.*, people_attributes.content (wobei hier der people_attributes Eintrag gemeint ist, der die Jahrgangsstufe enthält), storage_subjects.name



    Ich hoffe ich konnte diesen Datenbankausschnitt und das Abfrage-Ziel verständlich schildern. Ich probiere schon die ganze Zeit hin und her - kriege diese Abfrage so aber nicht hin. Vielleicht hat ja jemand von euch eine Idee? Wäre auf jeden Fall Klasse und ich würde mich sehr freuen.

    Viele Grüße

    Johnny
    Stichworte: join, kompliziert, php, sql
  • #2
    Fange mit subjectlinks an und einer entsprechenden where-Bedingung. Inner join auf die people, dann left join auf die people_attributes. Wo genau liegt nun dein Problem? Was hast du bereits probiert?
    [url]www.php-maven.org[/url] PHP und Maven vereint: Build/Deploy/Produktion/Konfiguration, Projekt Management, CI, PHPUnit, zahlreiche Frameworks
    Twitter @ [url]https://twitter.com/#!/mepeisen[/url] und Facebook @ [url]http://t.co/DZnKSUih[/url]

    Kommentar

    • #3
      Danke - nächstes Problem (

      Hallo mepeisen,

      vielen Dank für die Antwort. Hauptsächliches Problem bestand darin, dass ich noch keine Beispieldaten in `people_attributes` hatte ... ok peinlich

      Ich nutze nun folgende Abfrage:
      Code:
      SELECT
    `people`.`pid` AS 'PID',
    `people`.`firstname` AS 'Vorname',
    `people`.`surname` AS 'Nachname',
    `people_attributes`.`content` AS 'Stufe',
    `storage_subjects`.`name` AS 'Fach'
FROM `shs_subjectlinks`
INNER JOIN `people` ON `people`.`pid` = `shs_subjectlinks`.`pid`
LEFT JOIN `storage_subjects` ON `storage_subjects`.`sid` = `shs_subjectlinks`.`sid`
LEFT JOIN `people_attributes` ON `people_attributes`.`pid` = `people`.`pid`
LEFT JOIN `storage_attributes` ON `storage_attributes`.`aid` = `people_attributes`.`attribute`
WHERE
    `shs_subjectlinks`.`teach` = '1'
    AND `storage_attributes`.`key` = 'grade'
      Mit Daten in der Tabelle `people_attributes` liefert der Query nun auch Daten. Allerdings werden auch nur die Personen angezeigt, die auch einen Eintrag vom Attribut-Typ "1" in `people_attributes` haben. Müsste LEFT JOIN nicht eigentlich dazu führen, dass die anderen auch angezeigt werden und in der entsprechenden Spalte dann NULL haben - bzw. gibt es eine Möglichkeit, wie ich das realisieren könnte?

      Danke schon einmal im Voraus.

      Gruß, Johnny

      Kommentar

      • #4
        Mach mal ein konkretes Beispiel bitte. Ich stehe entweder gerade auf dem Schlauch oder ich sehe nicht, welches Attribut du meinst. Das aus deiner Where-Bedingung bezieht sich gar ncht auf die Tabelle wo du meinst oder?
        Generell ist bei solchen Fällen immer ratsam, die Wehere-Bedingung (in deinem Fall auf `storage_attributes`.`key``) auch auf Null zu ändern. Also:
        Code:
        and (`storage_attributes`.`key` = 'grade' or `storage_attributes`.`key` is null)
        Ebend weil dein where sonst das, was dir das left join ermöglicht (ausgabe der zeilen wo kein datensatz gefunden wurde) wieder zunichte macht. Where bezieht sich an dieser stelle immer auf das endergebnis nach allen joins.
        [url]www.php-maven.org[/url] PHP und Maven vereint: Build/Deploy/Produktion/Konfiguration, Projekt Management, CI, PHPUnit, zahlreiche Frameworks
        Twitter @ [url]https://twitter.com/#!/mepeisen[/url] und Facebook @ [url]http://t.co/DZnKSUih[/url]

        Kommentar

        • #5
          Zuletzt geändert von MrJSmith; 18.09.2011, 15:05. Grund: Rechtschreibfehler ;)
          Beispiel

          Danke für die Antwort. Dann versuche ich mal, es anhand folgendem Beispiel zu erklären:

          Tabelleninhalte
          Code:
          people
--------------------------
pid|firstname|surname|etc.
--------------------------
1  |Peter    |Meier  |... 
2  |Paul     |Schulz |... 
3  |Hans     |Wurst  |... 

shs_subjectlinks
------------------------
psid|pid|sid|teach|learn
------------------------
1   |1  |2  |1    |0
2   |2  |3  |1    |0
3   |1  |2  |0    |1
4   |3  |5  |0    |1

people_attributes
--------------------------------------
paid|pid|attribute|content
--------------------------------------
1   |1  |grade    |13
3   |3  |grade    |6
4   |1  |school   |Gymnasium Berlin
5   |2  |school   |Baumschule Hannover
6   |3  |school   |Baumschule Hannover

storage_attributes
-----------------------
aid|key   |name
-----------------------
1  |grade |Jahrgangsstufe
2  |school|Schule

storage_subjects
-----------------------
sid|name
-----------------------
1  |Deutsch
2  |Englisch
3  |Mathematik
4  |Latein
          Der Abfrage entwurf sollte im Prinzip folgendermaßen aussehen:

          Code:
          PID|Vorname|Nachname|Stufe|Fach
1  |Peter  |Meier   |13   |Englisch
2  |Paul   |Schulz  |NULL |Mathematik
          Bei mir schaut's aktuell leider so aus (weil für Person PID=2(Schulz) kein "grade"-Eintrag in "people_attributes" existiert):

          Code:
          PID|Vorname|Nachname|Stufe|Fach
1  |Peter  |Meier   |13   |Englisch

          Wenn wir dieses Problem lösen könnten wäre das schonmal sehr gut. Wenn man jetzt sogar noch die Möglichkeit hätte, auch noch die Schule im Abfrageergebnis anzuzeigen, wäre das natürlich genial. Allerdings müsste man dann ja innerhalb einer Abfrage zweimal auf "people_attributes.content" verlinken, wobei das eine die Jahrgangsstufe und das andere die Schule enthält. Wenn da jemand eine Idee hätte, wäre das natürlich die High-End-Lösung

          Vielen Dank für eure Hilfe schonmal.
          Liebe Grüße
          Johnny

          Kommentar

          • #6
            Lies mal meinen vorherigen Beitrag. Und denn ausprobieren.
            [url]www.php-maven.org[/url] PHP und Maven vereint: Build/Deploy/Produktion/Konfiguration, Projekt Management, CI, PHPUnit, zahlreiche Frameworks
            Twitter @ [url]https://twitter.com/#!/mepeisen[/url] und Facebook @ [url]http://t.co/DZnKSUih[/url]

            Kommentar

            • #7
              Die zusätzliche WHERE-Bedingung
              Code:
               or `storage_attributes`.`key` is null)
              habe ich ausprobiert. Das hilft leider nicht - würde glaube ich aber auch nicht zum Ziel führen, da es ja darum geht, die Klasse und nicht die Schule oder irgendetwas anderes in der Zeile anzuzeigen, oder?

              Das aus deiner Where-Bedingung bezieht sich gar ncht auf die Tabelle wo du meinst oder?
              --> Da weiß ich nicht genau, wie du das meinst?!

              Vielen Dank schon mal!

              Kommentar

              • #8
                Ich habe jetzt erkannt, das ein Tabellen Joint hier keine Lösung bringt. Einzige Lösung geht m.E. über Subqueries - dann auch mit Schule:
                Code:
                SELECT
   `people`.`pid` AS `PID`,
   `people`.`firstname` AS `Vorname`,
   `people`.`surname` AS `Nachname`,
   (SELECT `pa1`.`content` FROM (`people_attributes` AS `pa1` LEFT JOIN `storage_attributes` AS `spa1` ON ((`spa1`.`aid` = `pa1`.`attribute`))) WHERE ((`spa1`.`key` = 'school') and (`pa1`.`pid` = `people`.`pid`)) limit 1) AS `Schule`,
   (SELECT `pa2`.`content` FROM (`people_attributes` AS `pa2` LEFT join `storage_attributes` AS `spa2` ON ((`spa2`.`aid` = `pa2`.`attribute`))) WHERE ((`spa2`.`key` = 'grade') and (`pa2`.`pid` = `people`.`pid`)) limit 1) AS `Stufe`,
   `storage_subjects`.`name` AS `Fach`
FROM ((`shs_subjectlinks` INNER JOIN `people` ON ((`people`.`pid` = `shs_subjectlinks`.`pid`))) LEFT JOIN `storage_subjects` ON ((`storage_subjects`.`sid` = `shs_subjectlinks`.`sid`)))
WHERE ((`shs_subjectlinks`.`teach` = 1) and (`shs_subjectlinks`.`learn` = 0))
ORDER BY
   `storage_subjects`.`name`,
   `people`.`surname`,
   `people`.`firstname`
                Vielen Dank trotzdem!

                Kommentar

                Vorherige Weiter
                Lädt...
                X