knifflige Verknüpfungsaufgabe - leider ohne Subselects

[dedlfix]

Guten Morgen!

Gegeben seien drei Tabellen einer MySQL 4.0-Datenbank:

+-------+   +------------------+   +---------------+
|Persons|   |Connections       |   |ConnectionTypes|
+-------+   +------------------+   +---------------+
|ID     |-+ |ID                | +-|ID             |
|Name   | +-|ID_Persons        | | |Name           |
|...    |   |ID_ConnectionTypes|-+ +---------------+
+-------+   |Value             |
            |...               |
            +------------------+

ConnectionTypes enthält in Name beispielsweise Werte wie Phone, Fax, Email.

Ziel ist es Persons.*, Connections.Value AS Telefon, Connections.Value AS Fax zu erhalten. Weiterhin soll die Ergebnismenge zählbar, limitierbar und einschränkbar sein. (Wie das geht ist mir klar, es sollte nur bei der Lösung berücksichtigt werden.)

Pro Person und ConnectionType gibt es höchstens einen Datensatz (das wird durch eine entsprechende Bedingung sichergestellt).

Für Copy&Paste gibt es hier die CREATEs und ein paar Daten:
CREATE TABLE ConnectionTypes (ID int(11), Name varchar(20), PRIMARY KEY(ID));
CREATE TABLE Connections (ID int(11), ID\_Persons int(11), ID\_ConnectionTypes int(11), Value varchar(50), PRIMARY KEY(ID));
CREATE TABLE Persons (ID int(11), Name varchar(50), PRIMARY KEY(ID));

INSERT INTO Persons VALUES (1,'Person1'),(2,'Person2'),(3,'Person3');
INSERT INTO ConnectionTypes VALUES (1,'Telefon'),(2,'Fax'),(3,'Email');
INSERT INTO Connections VALUES (1,1,1,'0815-4711'),(2,1,2,'0815-4712'),(3,2,1,'0815-4721'),(4,2,2,'0815-4722'),(5,3,1,'0815-4731');

Mein bester bisheriger Versuch sieht so aus:

SELECT Persons.*, ConnectionsPhone.Value AS 'Telefon', ConnectionsFax.Value AS 'Fax'

FROM Persons
  LEFT JOIN Connections AS ConnectionsPhone
    ON Persons.ID = ConnectionsPhone.ID\_Persons
  JOIN ConnectionTypes AS ConnectionTypesPhone
    ON ConnectionsPhone.ID\_ConnectionTypes = ConnectionTypesPhone.ID
      AND ConnectionTypesPhone.Name = 'Telefon'

LEFT JOIN Connections AS ConnectionsFax
    ON Persons.ID = ConnectionsFax.ID\_Persons
  JOIN ConnectionTypes AS ConnectionTypesFax
    ON ConnectionsFax.ID\_ConnectionTypes = ConnectionTypesFax.ID
      AND ConnectionTypesFax.Name = 'Fax';

Das klappt soweit, jedoch gibt es keine Datensätze, wenn die Person beispielsweise kein Fax hat (Person3).

War das jetzt knifflig genug? :-)
Wie gehts besser?

[Bio]

Sup!

Besser geht es mit einer vernünftigen Datenbank - Oracle oder PostGreSQL. Diese Join-Syntax von mySQL ist ja grauenhaft.

Und wie ist das eigentlich... Für jeden Connection-Type kann jeder Person genau einen Eintrag in der Connections-Table haben, oder?

Dann könnte es doch auch Leute ohne Telefon geben?

Gruesse,

Bio

[dedlfix]

Sup!

Besser geht es mit einer vernünftigen Datenbank - Oracle oder PostGreSQL. Diese Join-Syntax von mySQL ist ja grauenhaft.

Ich hab das mit SubSelects unter MySQL 4.1 schon "vernünftig" hinbekommen... Leider kann ich nur 4.0 verwenden. :-(

Und wie ist das eigentlich... Für jeden Connection-Type kann jeder Person genau einen Eintrag in der Connections-Table haben, oder?
Dann könnte es doch auch Leute ohne Telefon geben?

Jein, jede Person kann zwar mehrere Connections vom selben Typ haben, eine einzige davon ist jedoch jeweils speziell gekennzeichnet (IsPrimary), diese möchte ich haben. Für das Beispiel hab ich dieses Feld weggelassen, es ist so schon komplex genug :-( Die entsprechende WHERE-Klausel werde ich dann am Ende schon noch berücksichtigen. D.h. für diese Ausgabe reicht es deshalb, anzunehmen, dass nur nur 0 oder eine Connection pro Person und ConnectionsTyp existiert.

dedlfix

[Andreas Lindig]

Hallo,

mit SubSelects unter MySQL 4.1 schon "vernünftig" hinbekommen... Leider kann ich nur 4.0 verwenden. :-(

Alles, was Du mit Subselect kannst, kannst Du auch mit einer temp-Tabelle imitieren. Abfrage 1 - in tmp speichern (subselect), dann tmp abfragen (Hauptselect). Mußt in MySQL 4 nur drauf achten, daß tmp-Tabellen erlaubt sind, das ist nicht mehr default-Standard.

Gruß, Andreas

[dedlfix]

Alles, was Du mit Subselect kannst, kannst Du auch mit einer temp-Tabelle imitieren. Abfrage 1 - in tmp speichern (subselect), dann tmp abfragen (Hauptselect).

Das Verwenden einer temporären Tabelle scheint die einzig brauchbare Lösung des Problems zu sein.

Ich hab das nun (auf des gegebene Bespiel bezogen) so gelöst:

CREATE TEMPORARY TABLE temptable SELECT * FROM Persons;
ALTER TABLE temptable ADD ( Telefon varchar(50), Fax varchar(50) );
UPDATE temptable, Connections, ConnectionTypes
  SET temptable.Telefon=Connections.Value
  WHERE temptable.ID=Connections.ID\_Persons AND
    Connections.ID\_ConnectionTypes=ConnectionTypes.ID AND
    ConnectionTypes.Name='Telefon';
UPDATE temptable, Connections, ConnectionTypes
  SET temptable.Fax=Connections.Value
  WHERE temptable.ID=Connections.ID\_Persons AND
    Connections.ID\_ConnectionTypes=ConnectionTypes.ID AND
    ConnectionTypes.Name='Fax' AND Connections.IsPrimary=1;

(In dem zweiten Update habe ich das erwähnte IsPrimary-Feld beispielhaft mit eingebaut.)

Das ist zwar etwas aufwändiger aber dafür kann man nun schön SELECTen ohne rumzuJOINen :-)

Dank allen, die mitgeknobelt haben.
dedlfix

[Andreas Lindig]

Dank allen, die mitgeknobelt haben.

was gibt's denn als Preis? ;-)

Gruß, Andreas

[dedlfix]

Dank allen, die mitgeknobelt haben.

was gibt's denn als Preis? ;-)

Für mich Monetäres vom Kunden.

Ihr (eigentlich: wir™, ich will mich da nicht ausschließen) hier macht das doch aus Spaß an der Freude... :-)

[Axel Richter]

Hallo,

Besser geht es mit einer vernünftigen Datenbank - Oracle oder PostGreSQL. Diese Join-Syntax von mySQL ist ja grauenhaft.

Worin unterscheidet sich die JOIN-Syntax von PostgreSQL von der von MySQL und von der von ANSI-SQL?

viele Grüße

Axel

[Bio]

Sup!

Nun, da kann man schreiben

SELECT e.name, d.dept
FROM employees e, departments d
WHERE e.dept = dept.id;

Also ein JOIN sieht da gleich viel weniger kompliziert und eher logisch und einfach aus - finde ich.

Gruesse,

Bio

[Vinzenz]

Hallo Bio,

SELECT e.name, d.dept
FROM employees e, departments d
WHERE e.dept = dept.id;

Das wird von MySQL selbstverständlich ebenfalls unterstützt.

Also ein JOIN sieht da gleich viel weniger kompliziert und eher logisch und einfach aus - finde ich.

Für mich persönlich sieht ein JOIN wesentlich unkomplizierter aus,
wenn ich die JOIN-Bedingung mit JOIN deklariere, sei es ein OUTER,
ein INNER, ein NATURAL oder (sehr sehr selten) ein CROSS JOIN ist.

Eine Frage des Geschmacks.

Freundliche Grüsse,

Vinzenz

[Sven Rautenberg]

Moin!

Pro Person und ConnectionType gibt es höchstens einen Datensatz (das wird durch eine entsprechende Bedingung sichergestellt).

Dann ist deine gesamte Mehrtabellenchose leider viel zu aufwendig, denn die Daten würden dann exakt in EINE Tabelle passen - naja, zumindest dann, wenn nicht ständig neue ConnectionTypes hinzukommen. Der Witz deiner Aufteilung ist doch gerade, dass man auf diese Weise für eine Person beliebig viele Telefonnummern speichern könnte.

Das klappt soweit, jedoch gibt es keine Datensätze, wenn die Person beispielsweise kein Fax hat (Person3).

Wann immer es aufgrund von fehlenden Datensätzen zu NULL-Eintragungen kommen muß, sollte man die ganze Bandbreite von JOINs durchtesten. OUTER JOIN beispielsweise.

- Sven Rautenberg

[dedlfix]

Moin!

Pro Person und ConnectionType gibt es höchstens einen Datensatz (das wird durch eine entsprechende Bedingung sichergestellt).

Dann ist deine gesamte Mehrtabellenchose leider viel zu aufwendig, denn die Daten würden dann exakt in EINE Tabelle passen - naja, zumindest dann, wenn nicht ständig neue ConnectionTypes hinzukommen. Der Witz deiner Aufteilung ist doch gerade, dass man auf diese Weise für eine Person beliebig viele Telefonnummern speichern könnte.

Genauso ist es.
Speziell für diese Abfrage ist jedoch nur die Ausgabe der primären Telefonnummer, Fax, ... gewünscht. (Es gibt dazu in Connections u.a. noch ein Feld IsPrimary, das, durch Programmlogik sichergestellt, pro ConnectionType und Person nur einmal 1/true enthält.)

Das klappt soweit, jedoch gibt es keine Datensätze, wenn die Person beispielsweise kein Fax hat (Person3).

Wann immer es aufgrund von fehlenden Datensätzen zu NULL-Eintragungen kommen muß, sollte man die ganze Bandbreite von JOINs durchtesten. OUTER JOIN beispielsweise.

Ein LEFT JOIN ist doch ein LEFT OUTER JOIN oder nicht?
Wenn ich ConnectionTypes auch LEFT JOINe kommen falsche Verknüpfungen raus, da stehen dann alle ConnectionTypes unter allen Ergebnisfeldern. :-(

dedlfix

[Axel Richter]

Hallo,

SELECT Persons.*, ConnectionsPhone.Value AS 'Telefon', ConnectionsFax.Value AS 'Fax'

FROM Persons
   LEFT JOIN (Connections AS ConnectionsPhone
               JOIN ConnectionTypes AS ConnectionTypesPhone
               ON (ConnectionsPhone.ID\_ConnectionTypes = ConnectionTypesPhone.ID AND ConnectionTypesPhone.Name = 'Telefon'))
   ON Persons.ID = ConnectionsPhone.ID\_Persons

LEFT JOIN (Connections AS ConnectionsFax
               JOIN ConnectionTypes AS ConnectionTypesFax
               ON (ConnectionsFax.ID\_ConnectionTypes = ConnectionTypesFax.ID AND ConnectionTypesFax.Name = 'Fax'))
   ON Persons.ID = ConnectionsFax.ID\_Persons

ungetestet, aber die Struktur erscheint mir so logischer.

viele Grüße

Axel

[dedlfix]

ungetestet, aber die Struktur erscheint mir so logischer.

Mir auch und das hatte ich auch schon ohne Erfolg probiert.
Ergebnis für die Beispiel-Daten: 75 Zeilen :-(

dedlfix