[Erledigt] kompliziertere SQL Abfrage / mehrere Joins

MrJSmith · 17.09.2011, 23:11

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

mepeisen · 18.09.2011, 09:08

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?

MrJSmith · 18.09.2011, 11:56

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

mepeisen · 18.09.2011, 13:41

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.

MrJSmith · 18.09.2011, 16:04

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

mepeisen · 18.09.2011, 16:30

Lies mal meinen vorherigen Beitrag. Und denn ausprobieren.

MrJSmith · 18.09.2011, 16:33

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?

Zitat:

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!

MrJSmith · 19.09.2011, 13:04

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!

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
SQL Abfrage pro Id in eine Zeile zusammenführen	martin.b07	Datenbanken	3	25.08.2011 22:30
Ist SQL Abfrage leer?	zarabina	Datenbanken	2	25.08.2011 11:27
SQL Abfrage optimieren	tromton	Datenbanken	11	01.08.2011 20:42
[Erledigt] SQL Abfrage escapen	wooha	PHP Einsteiger	3	18.04.2011 10:13
[Erledigt] Verdraxte Abfrage von 2 SQL Tabellen	EmmKey	PHP Einsteiger	7	15.03.2011 12:21
[Erledigt] SQL zweistufige Abfrage	Elefterios	Datenbanken	12	13.03.2011 15:23
SQL Abfrage INNER JOIN etc.	chr1s-eg	Datenbanken	4	01.03.2011 13:37
Array aus SQL Abfrage zusammenbauen um es rekursiv abzuarbeiten	litterauspirna	PHP Einsteiger	7	01.02.2011 18:44
Werte mehrerer Checkboxen für SQL Abfrage übernehmen?	krais	PHP Einsteiger	4	14.01.2011 21:24
Umlaute (Ã¤) in der SQL Abfrage	noop	Datenbanken	5	09.01.2011 23:06
[Erledigt] Mehr als eine SQL Abfrage hintereinander.	Seebär	PHP Einsteiger	5	29.12.2010 15:44
erweitere sql abfrage	Helix	PHP Einsteiger	5	27.12.2010 04:11
False Abfrage und SQL hängt sich auf	marc77	Datenbanken	4	22.11.2010 14:57
[Erledigt] sql abfrage mit Tabelle in der $ Zeichn vorkommt	dior	Datenbanken	5	09.11.2010 13:05
[Erledigt] Abfrage aus mehreren Normalisierten Tabellen (JOINS)	defcut	Datenbanken	4	25.08.2009 00:27

17.09.2011, 23:11
MrJSmith Neuer Benutzer Registriert seit: 17.09.2011 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	[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


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

18.09.2011, 09:08
mepeisen Erfahrener Benutzer Registriert seit: 21.12.2004 Beiträge: 5.233 PHP-Kenntnisse: Fortgeschritten	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? __________________ www.php-maven.org PHP und Maven vereint: Build/Deploy/Produktion/Konfiguration, Projekt Management, CI, PHPUnit, zahlreiche Frameworks Twitter @ https://twitter.com/#!/mepeisen und Facebook @ http://t.co/DZnKSUih

18.09.2011, 11:56
MrJSmith Neuer Benutzer Registriert seit: 17.09.2011 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	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

18.09.2011, 13:41
mepeisen Erfahrener Benutzer Registriert seit: 21.12.2004 Beiträge: 5.233 PHP-Kenntnisse: Fortgeschritten	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. __________________ www.php-maven.org PHP und Maven vereint: Build/Deploy/Produktion/Konfiguration, Projekt Management, CI, PHPUnit, zahlreiche Frameworks Twitter @ https://twitter.com/#!/mepeisen und Facebook @ http://t.co/DZnKSUih

18.09.2011, 16:04
MrJSmith Neuer Benutzer Registriert seit: 17.09.2011 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	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 Geändert von MrJSmith (18.09.2011 um 16:05 Uhr). Grund: Rechtschreibfehler ;)

18.09.2011, 16:30
mepeisen Erfahrener Benutzer Registriert seit: 21.12.2004 Beiträge: 5.233 PHP-Kenntnisse: Fortgeschritten	Lies mal meinen vorherigen Beitrag. Und denn ausprobieren. __________________ www.php-maven.org PHP und Maven vereint: Build/Deploy/Produktion/Konfiguration, Projekt Management, CI, PHPUnit, zahlreiche Frameworks Twitter @ https://twitter.com/#!/mepeisen und Facebook @ http://t.co/DZnKSUih