Selbstverbindungen in MySQL
Vorbereitung
- Konsole
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE ARBEITER ( ARBEITER VARCHAR ( 255 ), NAME VARCHAR ( 255 ), VORARBEITER VARCHAR ( 255 ));
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '0', 'Mia Peschel', '5' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '1', 'Ben Plank', '5' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '2', 'Emma Eising', '2' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '3', 'Luis Pohl', '2' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '4', 'Hanna Schneider', '5' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '5', 'Jonas Mazziotti', '5' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '6', 'Sofia Schubert', '2' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '7', 'Paul Bergau', '2' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '8', 'Emilia Socha', '2' );
INSERT INTO ARBEITER ( ARBEITER, NAME, VORARBEITER ) VALUES ( '9', 'Adrian Dolzer-Brandenburg', '2' );
SELECT * FROM ARBEITER;+----------+---------------------------+-------------+
| ARBEITER | NAME | VORARBEITER |
+----------+---------------------------+-------------+
| 0 | Mia Peschel | 5 |
| 1 | Ben Plank | 5 |
| 2 | Emma Eising | 2 |
| 3 | Luis Pohl | 2 |
| 4 | Hanna Schneider | 5 |
| 5 | Jonas Mazziotti | 5 |
| 6 | Sofia Schubert | 2 |
| 7 | Paul Bergau | 2 |
| 8 | Emilia Socha | 2 |
| 9 | Adrian Dolzer-Brandenburg | 2 |
+----------+---------------------------+-------------+
In der Tabelle ist zu jedem Arbeiter sein Vorarbeiter eingetragen. Für einen Vorarbeiter ist er selber als Vorarbeiter eingetragen. Dies erlaubt es uns, eine Tabelle aller Vorarbeiter abzuleiten:
CREATE VIEW VORARBEITER AS
SELECT * FROM ARBEITER WHERE ARBEITER = VORARBEITER;SELECT * FROM VORARBEITER;
+----------+-----------------+-------------+
| ARBEITER | NAME | VORARBEITER |
+----------+-----------------+-------------+
| 2 | Emma Eising | 2 |
| 5 | Jonas Mazziotti | 5 |
+----------+-----------------+-------------+
Nun können wir die Tabelle der Arbeiter mit denen der Vorarbeiter verbinden, und so eine Tabelle erhalten, in welcher nach dem Namen eines Arbeiters der Name seines Vorarbeiters angegeben ist.
Der Pfeil der Abbildung entspricht dabei der Klausel »WHERE ARBEITER.VORARBEITER = VORARBEITER.ARBEITER«.
ARBEITER VORARBEITER
.----------------------------------------------------. .------------------------------------------.
| ARBEITER | NAME | VORARBEITER | | ARBEITER | NAME | VORARBEITER |
|----------------------------------------------------| |------------------------------------------|
| 0 | Mia Peschel | 5 | | 2 | Emma Eising | 2 |
| 1 | Ben Plank | 5 ------->| 5 | Jonas Mazziotti | 5 |
| 2 | Emma Eising | 2 | '------------------------------------------'
| 3 | Luis Pohl | 2 |
| 4 | Hanna Schneider | 5 |
| 5 | Jonas Mazziotti | 5 |
| 6 | Sofia Schubert | 2 |
| 7 | Paul Bergau | 2 |
| 8 | Emilia Socha | 2 |
| 9 | Adrian Dolzer-Brandenburg | 2 |
'----------------------------------------------------'SELECT
ARBEITER.NAME AS ARBEITER,
VORARBEITER.NAME AS VORARBEITERFROM
ARBEITER,
VORARBEITERWHERE
ARBEITER.VORARBEITER = VORARBEITER.ARBEITER;+---------------------------+-----------------+
| ARBEITER | VORARBEITER |
+---------------------------+-----------------+
| Emma Eising | Emma Eising |
| Luis Pohl | Emma Eising |
| Sofia Schubert | Emma Eising |
| Paul Bergau | Emma Eising |
| Emilia Socha | Emma Eising |
| Adrian Dolzer-Brandenburg | Emma Eising |
| Mia Peschel | Jonas Mazziotti |
| Ben Plank | Jonas Mazziotti |
| Hanna Schneider | Jonas Mazziotti |
| Jonas Mazziotti | Jonas Mazziotti |
+---------------------------+-----------------+
Wir haben in dieser Abfrage eine Tabelle mit einer aus ihr abgeleiteten Tabelle verbunden, so als ob dies zwei ganz verschiedenen Tabellen seien. Tatsächlich kann man sich für diesen Zweck die abgeleitete Tabelles des Views wie eine selbständige Tabelle vorstellen.
Es ist nicht einmal nötig das zweite View derselben Tabelle als ein View anzulegen, man kann eine Tabelle auch direkt mit sich selbst verbinden. Zur eindeutigen Bezeichnung von Spalten ist es dafür erforderlich der „zweiten Kopie“ der Tabelle in der FROM-Klausel einen eigenen Namen zu geben. Das zuvor angelegte View wird hier nun nicht mehr benötigt.
Der Pfeil der Abbildung entspricht dabei der Klausel »WHERE ARBEITER.VORARBEITER = VORARBEITER.ARBEITER«.
ARBEITER VORARBEITER (ARBEITER AS VORARBEITER)
.----------------------------------------------------. .----------------------------------------------------.
| ARBEITER | NAME | VORARBEITER | | ARBEITER | NAME | VORARBEITER |
|----------------------------------------------------| |----------------------------------------------------|
| 0 | Mia Peschel | 5 | | 0 | Mia Peschel | 5 |
| 1 | Ben Plank | 5 ------->| 1 | Ben Plank | 5 |
| 2 | Emma Eising | 2 | | 2 | Emma Eising | 2 |
| 3 | Luis Pohl | 2 | | 3 | Luis Pohl | 2 |
| 4 | Hanna Schneider | 5 | | 4 | Hanna Schneider | 5 |
| 5 | Jonas Mazziotti | 5 | | 5 | Jonas Mazziotti | 5 |
| 6 | Sofia Schubert | 2 | | 6 | Sofia Schubert | 2 |
| 7 | Paul Bergau | 2 | | 7 | Paul Bergau | 2 |
| 8 | Emilia Socha | 2 | | 8 | Emilia Socha | 2 |
| 9 | Adrian Dolzer-Brandenburg | 2 | | 9 | Adrian Dolzer-Brandenburg | 2 |
'----------------------------------------------------' '----------------------------------------------------'SELECT
ARBEITER.NAME AS ARBEITER,
VORARBEITER.NAME AS VORARBEITERFROM
ARBEITER,
ARBEITER AS VORARBEITERWHERE
ARBEITER.VORARBEITER = VORARBEITER.ARBEITER;+---------------------------+-----------------+
| ARBEITER | VORARBEITER |
+---------------------------+-----------------+
| Emma Eising | Emma Eising |
| Luis Pohl | Emma Eising |
| Sofia Schubert | Emma Eising |
| Paul Bergau | Emma Eising |
| Emilia Socha | Emma Eising |
| Adrian Dolzer-Brandenburg | Emma Eising |
| Mia Peschel | Jonas Mazziotti |
| Ben Plank | Jonas Mazziotti |
| Hanna Schneider | Jonas Mazziotti |
| Jonas Mazziotti | Jonas Mazziotti |
+---------------------------+-----------------+
Übungsaufgaben
/ Auflisten von Ehepaaren
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE MITGLIED
( MITGLIED VARCHAR ( 255 ), NAME VARCHAR ( 255 ), GESCHLECHT VARCHAR ( 255 ), EHEPARTNER VARCHAR ( 255 ));
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '0', 'Tshilidzi Ndebele', 'm', '-1' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '1', 'Katharina Babinsky', 'f', '-1' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '2', 'Judith Rest', 'f', '7' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '3', 'Frank Kemper', 'm', '5' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '4', 'Isabelle Kiser-Berendes', 'f', '-1' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '5', 'Christine Kemper', 'f', '3' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '6', 'Joachim Faigle', 'm', '-1' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '7', 'Alexander Michalke', 'm', '2' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '8', 'Karin Kuzke', 'f', '-1' );
INSERT INTO MITGLIED ( MITGLIED, NAME, GESCHLECHT, EHEPARTNER ) VALUES ( '9', 'Laura Wunder', 'f', '-1' );
SELECT * FROM MITGLIED;
In der Tabelle der Mitglieder eines Vereins haben Mitglieder über deren Ehepartner nichts bekannt ist, den Wert »-1« als Ehepartner.
Geben Sie eine Liste aller in der Tabelle eingetragenen Ehepaare aus, wobei in jeder Zeile nur die Namen der beiden Ehepartner vorkommen und jedes Ehepaar möglichst nur einmal ausgegeben wird.
+------------------+--------------------+
| MITGLIED | EHEPARTNER |
+------------------+--------------------+
| Christine Kemper | Frank Kemper |
| Judith Rest | Alexander Michalke |
+------------------+--------------------+
Hinweis Es ist übersichtlicher an Stelle von Unterabfragen, Views zu verwenden.
Hinweis Die Aufgabe wird einfacher, wenn man die Anforderung, jedes Ehepaar nur einmal auszugeben, ignoriert.