nicht angemeldet
Hallo Sven,
Nein, mache ich nicht. Das ist ja gerade der Trick mit dem ON-Statement: Es wird nicht das Kreuzprodukt (jede Zeile der einen Tabelle wird mit jeder anderen Zeile der anderen Tabelle verknüpft und dann rausgefiltert, was gewünscht ist) gebildet, sondern es werden nur die Verknüpfungen in die Ergebnistabelle gelegt, die der ON-Bedingung (geprüft vor der Verknüpfung) entsprechen.
Also, Sven, jetzt muss ich dich ruegen. Natuerlich tust du das. Du holst dir die erste Spalte per JOIN in die Ergebnismenge. Das Ergebnis sieht ca. so aus:
mysql> SELECT documents.id,documents.type,attributes.id,attributes.name,attributes.data FROM documents LEFT JOIN attributes ON attributes.did = documents.id WHERE documents.id = 1; +----+------+------+----------+------------+ | id | type | id | name | data | +----+------+------+----------+------------+ | 1 | 2 | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 2 | Received | FDBRcP8mba | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 5 | To | Vlb4QscpUT | | 1 | 2 | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 7 | From | wSLfs2mLG5 | +----+------+------+----------+------------+ 7 rows in set (0.01 sec)
mysql>
Jetzt holst du dir die 'attributes'-Spalte ein weiteres mal hinzu, auch ueber einen JOIN:
mysql> SELECT documents.id,documents.type,attributes.id,attributes.name,attributes.data,a2.id,a2.name,a2.data FROM documents LEFT JOIN attributes ON attributes.did = documents.id LEFT JOIN attributes AS a2 ON a2.did = documents.id WHERE documents.id = 1; +----+------+------+----------+------------+------+----------+------------+ | id | type | id | name | data | id | name | data | +----+------+------+----------+------------+------+----------+------------+ | 1 | 2 | 1 | Received | 61VlXQaSUQ | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 1 | Received | 61VlXQaSUQ | 2 | Received | FDBRcP8mba | | 1 | 2 | 1 | Received | 61VlXQaSUQ | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 1 | Received | 61VlXQaSUQ | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 1 | Received | 61VlXQaSUQ | 5 | To | Vlb4QscpUT | | 1 | 2 | 1 | Received | 61VlXQaSUQ | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 1 | Received | 61VlXQaSUQ | 7 | From | wSLfs2mLG5 | | 1 | 2 | 2 | Received | FDBRcP8mba | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 2 | Received | FDBRcP8mba | 2 | Received | FDBRcP8mba | | 1 | 2 | 2 | Received | FDBRcP8mba | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 2 | Received | FDBRcP8mba | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 2 | Received | FDBRcP8mba | 5 | To | Vlb4QscpUT | | 1 | 2 | 2 | Received | FDBRcP8mba | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 2 | Received | FDBRcP8mba | 7 | From | wSLfs2mLG5 | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | 2 | Received | FDBRcP8mba | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | 5 | To | Vlb4QscpUT | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 3 | Cc | 9PRNBWUEsS | 7 | From | wSLfs2mLG5 | | 1 | 2 | 4 | Subject | pfj6fC.tNy | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 4 | Subject | pfj6fC.tNy | 2 | Received | FDBRcP8mba | | 1 | 2 | 4 | Subject | pfj6fC.tNy | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 4 | Subject | pfj6fC.tNy | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 4 | Subject | pfj6fC.tNy | 5 | To | Vlb4QscpUT | | 1 | 2 | 4 | Subject | pfj6fC.tNy | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 4 | Subject | pfj6fC.tNy | 7 | From | wSLfs2mLG5 | | 1 | 2 | 5 | To | Vlb4QscpUT | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 5 | To | Vlb4QscpUT | 2 | Received | FDBRcP8mba | | 1 | 2 | 5 | To | Vlb4QscpUT | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 5 | To | Vlb4QscpUT | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 5 | To | Vlb4QscpUT | 5 | To | Vlb4QscpUT | | 1 | 2 | 5 | To | Vlb4QscpUT | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 5 | To | Vlb4QscpUT | 7 | From | wSLfs2mLG5 | | 1 | 2 | 6 | Bcc | qroXa5C1.n | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 6 | Bcc | qroXa5C1.n | 2 | Received | FDBRcP8mba | | 1 | 2 | 6 | Bcc | qroXa5C1.n | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 6 | Bcc | qroXa5C1.n | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 6 | Bcc | qroXa5C1.n | 5 | To | Vlb4QscpUT | | 1 | 2 | 6 | Bcc | qroXa5C1.n | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 6 | Bcc | qroXa5C1.n | 7 | From | wSLfs2mLG5 | | 1 | 2 | 7 | From | wSLfs2mLG5 | 1 | Received | 61VlXQaSUQ | | 1 | 2 | 7 | From | wSLfs2mLG5 | 2 | Received | FDBRcP8mba | | 1 | 2 | 7 | From | wSLfs2mLG5 | 3 | Cc | 9PRNBWUEsS | | 1 | 2 | 7 | From | wSLfs2mLG5 | 4 | Subject | pfj6fC.tNy | | 1 | 2 | 7 | From | wSLfs2mLG5 | 5 | To | Vlb4QscpUT | | 1 | 2 | 7 | From | wSLfs2mLG5 | 6 | Bcc | qroXa5C1.n | | 1 | 2 | 7 | From | wSLfs2mLG5 | 7 | From | wSLfs2mLG5 | +----+------+------+----------+------------+------+----------+------------+ 49 rows in set (0.01 sec)
mysql>
So, und was ist das anderes als ein karthesisches Produkt aus den beiden JOIN-Tabellen?
Ueberigens ist deine Abfrage eh falsch ;) Sie wuerde auch wieder nur eine Reihe bringen, wegen des JOIN ueber die Attributs-ID. Ich bin zu dem Schluss gekommen, dass folgendes die einzige Moeglichkeit ist:
SELECT documents.id, documents.type, attributes.id AS aid, attributes.name, attributes.data FROM documents LEFT JOIN attributes ON attributes.did = documents.id LEFT JOIN attributes AS a2 ON a2.did = documents.id WHERE a2.name = "username" AND a2.data = "ckruse";
Die Ergebnismenge ist (korrekterweise):
+-------+------+-------+----------+---------+ | id | type | aid | name | data | +-------+------+-------+----------+---------+ | 10001 | 5 | 45110 | username | ckruse | | 10001 | 5 | 45111 | password | gjm270z | | 10001 | 5 | 45112 | nname | Kruse | +-------+------+-------+----------+---------+
Das ist allerdings etwas lahmarschig, wie gesagt. Deshalb werde ich das wohl in zwei Selects machen.
Gruesse, CK