• Sie befinden sich hier: Startseite > SQL-Backstube > Mit LEFT JOIN durch den Kontaktdschungel

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

20.02.2011: Mit LEFT JOIN durch den Kontaktdschungel

Nie gab es mehr Kontaktmöglichkeiten als heute und ständig kommen neue dazu. Bedeutet dies, ständig die Tabellen zu erweitern und jede Menge Leerfelder mit sich herum zu schleppen? Nein, mit dem richtigen Datenmodell und dem OUTER JOIN Befehl stehen wir als Datenbankentwickler dem gelassen gegenüber.

Um sicherzustellen, dass die verwendeten "eins zu viele" Fremdschlüsselbeziehungen (FOREIGN KEY) immer korrekte Ergebnisse liefern, wird die MySQL ENGINE InnoDb verwendet. Grundlage für stabile Fremdschlüsselbeziehungen sind die ON DELETE CASCADE bzw. ON DELETE RESTRICT Regeln.

Getestet mit folgender Datenbank:

* MySQL 5.1.44

Tabellenstrukturen und Testdaten

Listing 1:

1. /* Kontaktbezeichnungen */
2. CREATE TABLE kontakt_bez (
3. kontakt_bez_id INT NOT NULL,
4. bezeichnung VARCHAR(50) NOT NULL,
5. PRIMARY KEY (kontakt_bez_id)
6. ) ENGINE = InnoDB;
8. /* Personen */
9. CREATE TABLE person (
10. person_id INT NOT NULL,
11. nachname VARCHAR(50) NOT NULL,
12. PRIMARY KEY (person_id)
13. ) ENGINE = InnoDB;
15. /* person_hat_kontakt */
16. CREATE TABLE person_kontakt (
17. person_kontakt_id INT NOT NULL,
18. person_id INT NOT NULL,
19. kontakt_bez_id INT NOT NULL,
20. wert VARCHAR(50) NOT NULL,
21. bemerkung VARCHAR(50),
22. PRIMARY KEY (person_kontakt_id),
23. CONSTRAINT fk_person
24. FOREIGN KEY (person_id)
25. REFERENCES person (person_id)
26. ON DELETE CASCADE,
27. CONSTRAINT fk_kontakt_bez
28. FOREIGN KEY (kontakt_bez_id)
29. REFERENCES kontakt_bez (kontakt_bez_id)
30. ON DELETE RESTRICT
31. ) ENGINE = InnoDB;

Nun noch die Testdaten dazu einlesen. Beginnen wir mit den Kontaktarten. Diese Tabelle ist natürlich beliebig erweiterbar und damit haben wird auch kein Problem mit zukünftigen Ergänzungen.

Listing 2:

1. INSERT INTO kontakt_bez VALUES
2. ( 1, 'Telefon privat' ),
3. ( 2, 'Telefon geschäftlich' ),
4. ( 3, 'Telefax geschäftlich' ),
5. ( 5, 'E-Mail privat' ),
6. ( 6, 'E-Mail geschäftlich' ),
7. ( 7, 'Mobil privat' ),
8. ( 8, 'Mobil geschäftlich' ),
9. ( 9, 'Web-Seite' ),
10. (10, 'Facebook' ),
11. (11, 'Skype' ),
12. (12, 'Bankkonto Kaiman-Inseln' );

Hier ein paar Testpersonen und ein paar typische Kontaktdaten

Listing 3:

1. INSERT INTO person VALUES
2. ( 1, 'Vorname-1','Nachname-1' ),
3. ( 2, 'Vorname-2','Nachname-2' ),
4. ( 3, 'Vorname-3','Nachname-3' );
6. INSERT INTO person_kontakt VALUES
7. ( 1, 1, 1, '0049-0089-12345678' , NULL),
8. ( 2, 1, 9, 'https://twiedmann.de', NULL ),
9. ( 3, 2, 5, '[email protected]', NULL ),
10. ( 4, 2, 9, 'http://www.nachname2.de', NULL ),
11. ( 5, 2, 7, '0152-00000000', 'lange klingeln lassen!' ),
12. ( 6, 3, 2, '0049-0089-11111111', 'Sekretärin: Frau Immerda' ),
13. ( 7, 3,12, 'X12-all-ES-mEiNs', 'Passwort liegt beim Pförtner' );

SQL Abfragen

Zu allererst brauchen wir eine kleine Telefon-Liste aller angelegten Personen

Listing 4:

1. SELECT p.vorname, p.nachname, kb.bezeichnung, pk.wert
2. FROM person p
3. JOIN person_kontakt pk
4. ON p.person_id = ph.person_id
5. JOIN kontakt_bez kb
6. ON k.kontakt_id = pk.kontakt_id
7. WHERE pk.kontakt_id = 1 -- // 'Telefon privat'
8. ORDER BY p.nachname, p.vorname;
9. +-----------+------------+----------------+--------------------+
10. | vorname | nachname | bezeichnung | wert |
11. +-----------+------------+----------------+--------------------+
12. | Vorname-1 | Nachname-1 | Telefon privat | 0049-0089-12345678 |
13. +-----------+------------+----------------+--------------------+
14. 1 row in set (0.05 sec)

Hmm, die Abfrage in Listing 4 liefert allerdings nur die Personen, zu denen auch ein Telefon eingetragen ist. Macht nichts, da gibt es doch den OUTER JOIN. In unserem Fall also ein LEFT OUTER JOIN. Der Befehl OUTER ist optional und kann auch weggelassen werden.

Listing 5:

1. SELECT p.vorname, p.nachname, kb.bezeichnung, pk.wert
2. FROM person p
3. LEFT JOIN person_kontakt pk
4. ON p.person_id = pk.person_id
5. LEFT JOIN kontakt_bez kb
6. ON kb.kontakt_bez_id = pk.kontakt_bez_id
7. WHERE pk.kontakt_bez_id = 1 -- // 'Telefon privat'
8. ORDER BY p.nachname, p.vorname;
9. +-----------+------------+----------------+--------------------+
10. | vorname | nachname | bezeichnung | wert |
11. +-----------+------------+----------------+--------------------+
12. | Vorname-1 | Nachname-1 | Telefon privat | 0049-0089-12345678 |
13. +-----------+------------+----------------+--------------------+
14. 1 row in set (0.05 sec)

Zur allgemeinen Überraschung liefert auch diese Abfrage (Listing 5:) wieder nur die Personen, zu denen auch ein Telefon eingetragen ist, obwohl wir doch einen LEFT (OUTER) JOIN verwendet habe. Was läuft hier schief?

Listing 6:

1. SELECT p.vorname, p.nachname, kb.bezeichnung, pk.wert
2. FROM person p
3. LEFT JOIN person_kontakt pk
4. ON p.person_id = pk.person_id
5. AND pk.kontakt_bez_id = 1 -- // 'Telefon privat'
6. LEFT JOIN kontakt_bez kb
7. ON kb.kontakt_bez_id = pk.kontakt_bez_id
8. ORDER BY p.nachname, p.vorname;
9. +-----------+------------+----------------+--------------------+
10. | vorname | nachname | bezeichnung | wert |
11. +-----------+------------+----------------+--------------------+
12. | Vorname-1 | Nachname-1 | Telefon privat | 0049-0089-12345678 |
13. | Vorname-2 | Nachname-2 | NULL | NULL |
14. | Vorname-3 | Nachname-3 | NULL | NULL |
15. +-----------+------------+----------------+--------------------+
16. 3 rows in set (0.00 sec)

Aha, die gewünschte kontakt_bez_id muss auch im LEFT JOIN abgefragt werden. Bei Personen ohne hinterlegte Telefonnummer wird nun der Wert NULL ausgegeben.

..ausführliche Listen erwünscht

Im nächsten Schritt brauchen wir mehrere Kontaktdaten (Telefon, Web-Seite und E-Mail) auf einer Liste. Die Ausgabe der Daten soll zuerst untereinander und anschließend nebeneinander erfolgen. Damit bei nicht vorhandenen Werten keine NULL ausgegeben wird, nutzen wir das Kommando COALESCE, um einen leeren String ('') auszugeben.

Listing 7:

1. SELECT p.vorname, p.nachname,
2. COALESCE(kb.bezeichnung,'') AS bezeichnung,
3. COALESCE(pk.wert ,'') AS wert
4. FROM person p
5. LEFT JOIN person_kontakt pk
6. ON p.person_id = pk.person_id
7. AND pk.kontakt_bez_id IN (1,5,9)
8. LEFT JOIN kontakt_bez kb
9. ON kb.kontakt_bez_id = pk.kontakt_bez_id
10. ORDER BY p.nachname, p.vorname, kb.bezeichnung;
11. +-----------+------------+----------------+----------------------------+
12. | vorname | nachname | bezeichnung | wert |
13. +-----------+------------+----------------+----------------------------+
14. | Vorname-1 | Nachname-1 | Telefon privat | 0049-0089-12345678 |
15. | Vorname-1 | Nachname-1 | Web-Seite | https://twiedmann.de |
16. | Vorname-2 | Nachname-2 | E-Mail privat | [email protected] |
17. | Vorname-2 | Nachname-2 | Web-Seite | http://www.nachname2.de |
18. | Vorname-3 | Nachname-3 | | |
19. +-----------+------------+----------------+----------------------------+
20. 5 rows in set (0.03 sec)

Und hier nun eine Liste mit Telefon (id=1) und Email (id=5) horizontal dargestellt. Damit dies möglich wird, müssen die Tabelle kontakt_bez und person_kontakt je zweimal als LEFT JOIN aufgeführt werden

Listing 8:

1. SELECT p.vorname, p.nachname,
2. COALESCE(kb1.bezeichnung,'') AS bezeichnung1,
3. COALESCE(pk1.wert ,'') AS wert1,
4. COALESCE(kb5.bezeichnung,'') AS bezeichnung5,
5. COALESCE(pk5.wert , '') AS wert5
6. FROM person p
7. LEFT JOIN person_kontakt pk1
8. ON p.person_id = pk1.person_id
9. AND pk1.kontakt_bez_id = 1 -- // Telefon
10. LEFT JOIN kontakt_bez kb1
11. ON kb1.kontakt_bez_id = pk1.kontakt_bez_id
12. LEFT JOIN person_kontakt pk5
13. ON p.person_id = pk5.person_id
14. AND pk5.kontakt_bez_id = 5 -- // E-Mail
15. LEFT JOIN kontakt_bez kb5
16. ON kb5.kontakt_bez_id = pk5.kontakt_bez_id
17. ORDER BY p.nachname, p.vorname;
18. +-----------+------------+----------------+--------------------+---------------+----------------------------+
19. | vorname | nachname | bezeichnung1 | wert1 | bezeichnung5 | wert5 |
20. +-----------+------------+----------------+--------------------+---------------+----------------------------+
21. | Vorname-1 | Nachname-1 | Telefon privat | 0049-0089-12345678 | | |
22. | Vorname-2 | Nachname-2 | | | E-Mail privat | [email protected] |
23. | Vorname-3 | Nachname-3 | | | | |
24. +-----------+------------+----------------+--------------------+---------------+----------------------------+
25. 3 rows in set (0.00 sec)

Abschließend noch eine MySQL Spezialausgabe mit GROUP_CONCAT, bei der alle vorhandenen Kontakte als CSV-Daten dargestellt werden.

Listing 9:

1. SELECT p.vorname, p.nachname,
2. GROUP_CONCAT( CONCAT(kb.bezeichnung , ':' , pk.wert)
3. ORDER BY kb.bezeichnung ASC SEPARATOR ';'
4. ) AS kontakt
5. FROM person p
6. LEFT JOIN person_kontakt pk
7. ON p.person_id = pk.person_id
8. LEFT JOIN kontakt_bez kb
9. ON kb.kontakt_bez_id = pk.kontakt_bez_id
10. GROUP BY p.nachname, p.vorname
11. ORDER BY p.nachname, p.vorname;
12. +-----------+------------+-------------------------------------------------------------------------------------------------------+
13. | vorname | nachname | kontakt |
14. +-----------+------------+-------------------------------------------------------------------------------------------------------+
15. | Vorname-1 | Nachname-1 | Telefon privat:0049-0089-12345678;Web-Seite:https://twiedmann.de |
16. | Vorname-2 | Nachname-2 | E-Mail privat:[email protected];Mobil privat:0152-00000000;Web-Seite:http://www.nachname2.de |
17. | Vorname-3 | Nachname-3 | Bankkonto Kaiman-Inseln:X12-all-ES-mEiNs;Telefon geschäftlich:0049-0089-11111111 |
18. +-----------+------------+-------------------------------------------------------------------------------------------------------+
19. 3 rows in set (0.01 sec)

Resumee

Die SQL-Abfragen werden mit diesem Datenmodel zwar immer etwas umfangreicher, aber dafür bleibt es absolut flexibel, was mögliche zukünftige Änderungen angeht. Wer sich jemals mit einem nicht normalisierten Datenmodell herumgeschlagen hat, der weiß wovon ich hier rede.

https://twiedmann.de