Kreuzprodukt aus 5 Tabellen

[Martin]

Hallo,

ich habe 5 Tabellen (für jedes Jahr eine) mit der gleichen Struktur (ID,Name,...).
Damals hielt sich mein Wissen über eine gute Tabellenstruktur noch stark in Grenzen.

Ich möchte jetzt eine neue Tabelle machen damit ich weiß welches Mitglied im welchen Jahr welche ID hat.

Name | ID 2002 | ID 2003 | ID 2004
z.B.  Mayer|  1      |  4      |  NULL

Das Problem dabei ist das es Mitglieder gibt die für alle 5 Jahre (also in jeder Tabelle) einen Eintrag haben.
Aber auch welche die nur in den ersten 2 Jahren dabei waren bzw. erst letztes Jahr dazu gekommen sind.
Die ID kann dabei in jedem Jahr unterschiedlich sein.

Dazu hab ich in den letzten Tagen viel mit join-Abfragen herumexperimentiert aber es zu keinem zufriedenstellenden Ergebnis gebracht.

Ich bin nur soweit gekommen das ich die Namen/IDs aus dem aktuellen Jahr mit den IDs aus EINEM anderen Jahr ausgeben lasse.

SELECT pv06.name, pv06.id AS id06, pv04.id AS id04
FROM pv04
RIGHT JOIN pv06 ON pv04.name = pv06.name

Ist so eine Abfrage mit join überhaupts möglich?

Gruß,
Martin

[Vinzenz Mai]

Hallo Martin,

ich habe 5 Tabellen (für jedes Jahr eine) mit der gleichen Struktur (ID,Name,...).
Damals hielt sich mein Wissen über eine gute Tabellenstruktur noch stark in Grenzen.

es wäre eine sehr gute Idee, Deine Tabellenstruktur zu bereinigen.

Ich möchte jetzt eine neue Tabelle machen damit ich weiß welches Mitglied im welchen Jahr welche ID hat.

Name | ID 2002 | ID 2003 | ID 2004
z.B.  Mayer|  1      |  4      |  NULL

Das Problem dabei ist das es Mitglieder gibt die für alle 5 Jahre (also in jeder Tabelle) einen Eintrag haben.
Aber auch welche die nur in den ersten 2 Jahren dabei waren bzw. erst letztes Jahr dazu gekommen sind.
Die ID kann dabei in jedem Jahr unterschiedlich sein.

Dazu hab ich in den letzten Tagen viel mit join-Abfragen herumexperimentiert aber es zu keinem zufriedenstellenden Ergebnis gebracht.

Hier empfehle ich Dir fogende Artikel:
Einführung Joins
Fortgeschrittene Joins

Ist so eine Abfrage mit join überhaupts möglich?

Das hängt von Deinem Datenbankmanagementsystem (DBMS) ab.

Im folgenden möchte ich Dir eine unter MS SQL-Server (2000 und neuer) funktionierende Lösung vorstellen:

Ausgangstabellen:

Tabelle pv01
ID          name
----------- ------------
1           Martin
2           Jens
3           Marc

Tabelle pv02
ID          name
----------- ------------
1           Marc
2           Martin
3           Vinzenz

Tabelle pv03
ID          name
----------- ------------
1           Vinzenz
2           Martin
3           Rouven

gewünschtes Ergebnis:
name         ID 2001     ID 2002     ID 2003
------------ ----------- ----------- -----------
Martin       1           2           2
Jens         2           NULL        NULL
Marc         3           1           NULL
Vinzenz      NULL        3           1
Rouven       NULL        NULL        3

Um zum gewünschten Ergebnis zu gelangen verwende ich den FULL OUTER JOIN. Dieser wird nicht von jedem DBMS unterstützt, so z.B. von MySQL. Weiterhin gehe ich in meinem Beispiel davon aus, dass sich jedes Mitglied (unwahrscheinlicherweise) über seinen Namen eindeutig identifizieren läßt, d.h. als JOIN-Spalte verwende ich die Spalte "name".

1. Schritt: Ermittle die IDs der Jahre 2001 und 2002

SELECT  
    pv01.name,  
    pv02.name,  
    pv01.ID AS [ID 2001],  
    pv02.ID AS [ID 2002]  
FROM pv01  
FULL OUTER JOIN pv02  
ON pv01.name = pv02.name

Resultat:
name         name         ID 2001     ID 2002
------------ ------------ ----------- -----------
Martin       Martin       1           2
Jens         NULL         2           NULL
Marc         Marc         3           1
NULL         Vinzenz      NULL        3

Wie Du siehst, werden sowohl die Datensätze von Tabelle pv01 angezeigt, die keine Entsprechung in Tabelle pv02 haben, als auch die Datensätze von Tabelle pv02, die keine Entsprechung in Tabelle pv01 haben.

2. Schritt: Erzeuge die Spalte mit allen Namen.
Hier hilft die Funktion COALESCE(arg1, arg2, ...), die das erste von NULL verschiedene Argument zurückliefert.

SELECT  
    COALESCE(pv01.name, pv02.name) AS 'Mitglied',  
    pv01.name,  
    pv02.name,  
    pv01.ID AS [ID 2001],  
    pv02.ID AS [ID 2002]  
FROM pv01  
FULL OUTER JOIN pv02  
ON pv01.name = pv02.name

Resultat:
Mitglied     name         name         ID 2001     ID 2002
------------ ------------ ------------ ----------- -----------
Martin       Martin       Martin       1           2
Jens         Jens         NULL         2           NULL
Marc         Marc         Marc         3           1
Vinzenz      NULL         Vinzenz      NULL        3

3. Schritt: Tabelle pv03 mit hinzunehmen, wieder mit FULL OUTER JOIN
Wir joinen das Ergebnis der ersten Join-Operation (pv01 FULL OUTER JOIN pv02) mit der Tabelle pv03. Wiederum erfolgt die Join-Operation über die Spalte pv03.name. Als Partner kann weder pv01.name noch pv02.name dienen, da beide NULL-Werte enthalten (können). Aus diesem Dilemma hilft wieder die COALESCE-Funktion. Weiter habe ich die Namensspalten der Jahrestabellen aus dem Statement entfernt.

SELECT  
    COALESCE(pv01.name, pv02.name, pv03.name) AS 'name',  
    pv01.ID AS [ID 2001],  
    pv02.ID AS [ID 2002],  
    pv03.ID AS [ID 2003]  
FROM (pv01  
FULL OUTER JOIN pv02  
ON pv01.name = pv02.name)  
FULL OUTER JOIN pv03  
ON COALESCE(pv01.name, pv02.name) = pv03.name

liefert das gewünschte Resultat:
name         ID 2001     ID 2002     ID 2003
------------ ----------- ----------- -----------
Martin       1           2           2
Jens         2           NULL        NULL
Marc         3           1           NULL
Vinzenz      NULL        3           1
Rouven       NULL        NULL        3

(5 Zeile(n) betroffen)

Auf analoge Weise kann man das Statement auf fünf Tabellen erweitern.

Es gibt sicherlich auch andere Lösungsmöglichkeiten. Ob es für Dein DBMS eine Lösung gibt, die aus einem einzigen SQL-Statement besteht, kann ich Dir natürlich nicht sagen. Obige Lösung wurde mit der MS SQL Server 2005 Express Edition getestet.

Freundliche Grüße

Vinzenz

PS: Mit einem Kreuzprodukt hat Deine Tabelle nichts zu tun, sie erinnert an eine Kreuztabelle (auch Pivot-Tabelle genannt). Außer Jet-SQL, dem SQL-Dialekt der Jet-Engine (das DBMS hinter MS Access), kenne ich kein SQL-Dialekt, der Kreuztabellenabfragen direkt unterstützt.

[Frank (no reg)]

Hi Vinzenz,

PIVOT / UNPIVOT sind neue relationale Operatoren bei SQL 2005.

Grüsse zur Nacht.
Frank

[Martin]

Hallo Vinzenz,

erstmal vielen Dank für deine ausführliche Antwort.

es wäre eine sehr gute Idee, Deine Tabellenstruktur zu bereinigen.

Da bin ich doch gerade dabei ;)
Die neue User-Datenbank mit einer übergeordneten ID war der erste/schwierigste Schritt.

Ist so eine Abfrage mit join überhaupts möglich?

Das hängt von Deinem Datenbankmanagementsystem (DBMS) ab.

Um zum gewünschten Ergebnis zu gelangen verwende ich den FULL OUTER JOIN. Dieser wird nicht von jedem DBMS unterstützt, so z.B. von MySQL.

Leider hab ich vergessen zu schreiben, dass ich MySQL 4.1.10 verwende.
Es gibt zwar Umwege mit denenen man auch mit diesem DBMS einen FULL OUTER JOIN realisieren kann, aber damit bin ich nicht wirklich zurecht gekommen.
FULL OUTER JOIN
How to do a full outer join in mysql 3.23

Ich bin jetzt einen anderen Weg gegangen, der zwar umständlicher ist für mich einfacher war.

1. Eine neue Tabelle pv_user erstellt mit den Feldern: ID,name,id02,...,id06

INSERT INTO pv_user
SELECT 0 , pv02.name, pv02.id, 'x', 'x', 'x', 'x'
FROM pv02

INSERT INTO pv_user
SELECT 0 , pv03.name, 'x', pv03.id, 'x', 'x', 'x'
FROM pv03
LEFT JOIN pv_user
USING ( name )
WHERE pv_user.name IS NULL

...

2. Nach und nach die IDs der anderen Jahre hinzugefügt

SELECT u.name, pv02.id
FROM pv02, pv_user AS u
LEFT JOIN pv_user
USING ( name )
WHERE u.name = pv02.name
AND u.id02 =0

hier habe ich dann vergeblich versucht dieses SELECT als SUBQUERY in die UPDATE-Anweisung einzuarbeiten.
Bin dann den Umweg über eine PHP-Schleife gegangen:
update pv_user set id02 ='".$zelle['id']."' where name='".$zelle['name']."'"

3. Überprüfung ob alles stimmt

SELECT pv_user.name
FROM pv_user
WHERE pv_user.id02 !=0

und

SELECT pv02.name
FROM pv02

ergab die gleiche Anzahl.
Jetzt müsste eigentlich alles stimmen.

Gruß,
Martin