(MySQL) n:m-Relation

[Olaf Schneider]

Hallo alle zusammen,

ich habe in einer MySQL-Datenbank drei Tabellen:

table_links enthält Objekte
table_rechts enthält Eigenschaften
table_relation verbindet die beiden Tabellen über ihre id's n:m

Ich möchte jetzt für ein vor der SQL-Abfrage bekanntes Element E aus table_links eine Liste mit allen(!) Elementen aus table_rechts haben (Haha, das wäre ja SELECT * FROM table_rechts), dazu jedoch eine Pseudospalte, die zurückgibt, ob für das Element E eine Beziehung in table_relation zu table_rechts für die jeweilige Spalte existiert.

Leider komme ich mit den verschiedenen JOINS nicht weiter. Auch möchte ich UNION nicht benutzen, da die Abfrage auch vor Version 4 laufen soll.
Momentan habe ich das Problem in php gelöst, aber eigentlich ärgert mich das; das müßte doch auch anders gehen, oder?

Vielen Dank im voraus für alle Lösungsvorschläge

Gruß Olaf Schneider

[Michael Schröpl]

Hi Olaf Schneider,

dazu jedoch eine Pseudospalte, die zurückgibt, ob
für das Element E eine Beziehung in table_relation
zu table_rechts für die jeweilige Spalte existiert.

wofür brauchst Du das?

Leider komme ich mit den verschiedenen JOINS nicht
weiter.

Ich schätze, das liegt daran, daß Du nicht "in SQL denkst".
Eine Liste von Flags, wie Du sie erzeugen willst, ist keine 'natürliche' Datenstruktur von SQL. Dort würde man diese Information mit einer oder zwei Mengen ausdrücken - und diese kannst Du mit JOINs sicherlich berechnen.

Denke in Mengen, solange Du Dich in SQL bewegst.
Wenn Du diese Flags nur in einer algorithmischen Sprache brauchst, dann kannst Du sie auch noch am Interface zwischen diesen beiden Sprachen berechnen.

Auch möchte ich UNION nicht benutzen, da die Abfrage
auch vor Version 4 laufen soll.

Mir ist klar, daß Dein Problem mit einer besseren Datenbank(-version) sehr viel einfacher zu lösen wäre.
Gerade in Deinem Fall sind die reinen Mengenoperationen (ich würde den MINUS-Operator vorschlagen) das, was Du eigentlich brauchen würdest.

Da mySQL gerade hier seine Schwächen hat, solltest Du Deine Hoffnungen darauf leden, daß Du in Deiner Aufgabenstellung Platz für einen alternativen Lösungsansatz findest - deshalb habe ich Deinen Lösungsweg hinterfragt.
Du wirst mich erst überzeugen müssen, wofür Du diese Liste unbedingt in diesem Format brauchst.

Momentan habe ich das Problem in php gelöst, aber
eigentlich ärgert mich das; das müßte doch auch
anders gehen, oder?

Vielleicht nicht. Wenn Du eine Dose mit einer Nagelfeile zu öffnen versuchst, dann wird Dir das nicht so locker von der Hand gehen wie mit einem Dosenöffner.

Und es kann auch durchaus sein, daß Du ein 3GL-Konstrukt von PHP benötigst, um die Defizite der 4GL-Konstrukte von mySQL zu kompensieren - beispielsweise, indem Du den MINUS-Operator dort als Listenverarbeitung implementierst.

Viele Grüße
      Michael

[Olaf Schneider]

Hallo Michael,

Vielen Dank für Deine Antwort. Ich muß wohl etwas konkreter werden:

Ich möchte eine HTML-Seite erzeugen, die alle Eigenschaften listet, die in table_rechts enthalten sind, sowie eine Checkbox pro Eigenschaft, die genau dann angekreuzt ist, wenn ein bestimmtes - für die HTML Seite konstantes - Objekt diese Eigenschaft besitzt.

<beispiel>

Objekt 57
---------

Eigenschaft1
Eigenschaft2 x
Eigenschaft3 x
Eigenschaft4
Eigenschaft5 x
Eigenschaft6
Eigenschaft7 x
Eigenschaft8

-----------------------------------------

table_relation

id_objekt , id_eigenschaft

57,2
57,3
57,5
57,7

</beispiel>

Wenn es ein besseres DB-Modell gibt, ist mir das auch recht.

Gruss Olaf Schneider

P.S.: was ist 3GL / 4GL ?

[Sven Rautenberg]

Moin!

<beispiel>

Objekt 57

Eigenschaft1
Eigenschaft2 x
Eigenschaft3 x
Eigenschaft4
Eigenschaft5 x
Eigenschaft6
Eigenschaft7 x
Eigenschaft8

---

table_relation

id_objekt , id_eigenschaft

57,2
57,3
57,5
57,7

</beispiel>

Die Lösung deines Problems lautet "OUTER JOIN".

Wenn ich dich richtig verstanden habe, dann willst du für einerseits wissen, welche Eigenschaften es überhaupt gibt, und welche davon einem bestimmten Objekt zugeordnet sind.

Ausgehend von dieser Überlegung musst du von der Tabelle mit den Eigenschaften ausgehen und diese entsprechend SELECTieren. Darauf machst du dann einen OUTER JOIN mit deiner m:n-Tabelle, und meinetwegen dann noch einen JOIN auf die Objekttabelle.

Der OUTER JOIN (den MySQL wohl erst ab einer gewisse 3er-Version kann, keine Ahnung) sorgt dafür, dass auf jeden Fall alle Elemente der ersten Tabelle selektiert werden, und die Elemente der zweiten Tabelle nur bei Übereinstimmung zugeordnet werden - andernfalls wird NULL zugeordnet:

id bez.          | e-id  o-id
1  eigenschaft1  |  1    null
2  eigenschaft2  |  2    57
3  eigenschaft3  |  3    57
4  eigenschaft4  |  4    null
5  eigenschaft5  |  5    57
6  eigenschaft6  |  6    null
7  eigenschaft7  |  7    57

Wenn du diese Tabelle hast, kannst du recht leicht herausfinden, welche Eigenschaften es gibt, und welche davon dem Objekt 57 zugeordnet sind (und diese z.B. als Checkbox checked machen).

- Sven Rautenberg

[Olaf Schneider]

Hallo Sven,

vielen Dank für Deine Antwort.

Ich habe auch schon versucht, das Problem mit LEFT (OUTER) JOIN anzugehen, aber dabei folgendes Problem gehabt.

Wenn ich habe:

SELECT blabla FROM table_right r
LEFT OUTER JOIN table_relation rel
ON (r.rightid = rel.rightid)
WHERE rel.leftid= 57

erhalte ich nur die Reihen, bei denen das Objekt 57 auch wirklich in der Relation vorkomt.

2  eigenschaft2  |  2    57
3  eigenschaft3  |  3    57
5  eigenschaft5  |  5    57
7  eigenschaft7  |  7    57

Werfe ich das WHERE raus, so erhalte ich auch Reihen, in denen andere Objekte referenziert werden

1  eigenschaft1  |  1    55
1  eigenschaft1  |  1    56
2  eigenschaft2  |  2    53
2  eigenschaft2  |  2    57
u.s.w.

Weder mit der einen, noch mit der anderen Methode erhalte ich NULL-Werte.

Mit GROUP BY oder DISTINCT bin ich auch nicht weiter gekommen.

Falls Dir etwas einfällt, würde ich mich über Antwort freuen.

Gruß Olaf Schneider (geb. in Bremen, deswegen gibt's das nächste Mal auch'n Moin!)

[Sven Rautenberg]

Moin! :)

Wenn ich habe:

SELECT blabla FROM table_right r
LEFT OUTER JOIN table_relation rel
ON (r.rightid = rel.rightid)
WHERE rel.leftid= 57

erhalte ich nur die Reihen, bei denen das Objekt 57 auch wirklich in der Relation vorkomt.

2  eigenschaft2  |  2    57
3  eigenschaft3  |  3    57
5  eigenschaft5  |  5    57
7  eigenschaft7  |  7    57

Werfe ich das WHERE raus, so erhalte ich auch Reihen, in denen andere Objekte referenziert werden

1  eigenschaft1  |  1    55
1  eigenschaft1  |  1    56
2  eigenschaft2  |  2    53
2  eigenschaft2  |  2    57
u.s.w.

Ok, das ist aber vollkommen OK - für mySQL. Du mußt dir die Reihenfolge klarmachen, in der das Ergebnis generiert wird:

Zuerst kommt ein SELECT auf die Eigenschaften. Ergebnis ist eine Tabelle mit allen Eigenschaften, die es gibt - also die ganze Tabelle.

Dann kommt der OUTER JOIN ON xxx. Der ordnet entsprechend der Bedingung jeder Zeile der ersten Tabelle alle passenden Zeilen aus der zweite Tabelle zu, und wenn es keine passende Zeile gibt, wird eine NULL-Zeile zugeordnet.

In der Ergebnistabelle steht dann das drin, was du nach Weglassen des WHERE erreicht hast. Und erst in _dieser_ Tabelle kommt WHERE zur Anwendung und filtert nur die Zeilen heraus, die der WHERE-Bedingung entsprechen. Deine Bedingung ist aber reichlich ungeeignet: Da du in der Tabelle (selbst wenn sie NULL-Werte für die Objekt-ID enthalten würde) nur nach Objekt-ID 57 suchst, wirst du niemals NULL-Werte finden.

Die Lösung liegt darin, die Selektion der richtigen Objekt-ID schon _vorher_ anzuwenden - im ON-Statement:
ON (r.rightid = rel.rightid) AND (rel.leftid = 57)
Die WHERE-Bedingung darf dann komplett entfallen (es sei denn, du suchst in der Ergebnistabelle nach was besonderem, beispielsweise einer bestimmten Eigenschaft).

Auf diese Weise wird die hinzuzufügende Tabelle beim Hinzufügen gefiltert. Einer Zeile aus der linken Tabelle wird nur dann eine Zeile der rechten Tabelle zugeordnet, wenn die ON-Bedingung stimmt: Wenn die Zeile der rechten Tabelle die richtige Eigenschafts-ID hat und die richtige Objekt-ID. (Anmerkung: "Linke" und "rechte" Tabelle bezieht sich auf die Position im SELECT-Statement, nicht auf deine Tabellenbezeichnung - ich finde "links" und "rechts" in der Tabellenbezeichnung auch etwas sehr unglücklich gewählt, weil es einen so gerne in die Denkfalle lockt, die linke Tabelle eben immer links ins SELECT zu schreiben und so weiter.)

Ich hab's nicht getestet, aber es sollte funktionieren.

- Sven Rautenberg

[Olaf Schneider]

Moin,

ja es funktioniert. Da habe ich wieder was dazugelernt. Und natürlich nochmals vielen Dank! Wieder unnützen PHP-COde gespart - so mag ich das.

Gruss Olaf Schneider