SQL Ergebnismengen Problem mit GROUP BY (für Profis)

[Iomanip]

Hallo liebe Forenteilnehmer,

ich habe ein Problem mit folgender Abfrage in einer MySQL-Datenbank.

Datenbankstruktur:

CREATE TABLE IF NOT EXISTS `eltern` (

  `nachname` varchar(50) NOT NULL,

  `vorname` varchar(50) NOT NULL,

  `autos` int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `eltern` (`nachname`, `vorname`, `autos`) VALUES

('Dampf', 'Hans', 1),

('Dampf', 'Helga', 2),

('Maier', 'Sepp', 1);



CREATE TABLE IF NOT EXISTS `kinder` (

  `nachname` varchar(50) NOT NULL,

  `vorname` varchar(50) NOT NULL,

  `taschengeld` int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `kinder` (`nachname`, `vorname`, `taschengeld`) VALUES

('Dampf', 'Martin', 50),

('Dampf', 'Katja', 40),

('Dampf', 'Karina', 10),

('Maier', 'Sepp Junior', 30);

Abfrage: Folgende Abfrage verwende ich um das "erwartete" Ergebnis zu bekommen.

SELECT 

  k.nachname,

  COUNT(k.nachname) AS Kinderanzahl,

  COUNT(e.nachname) AS Elternanzahl,

  SUM(k.taschengeld) AS Taschengeld,

  SUM(e.autos) AS Anzahl_Autos

FROM `eltern` AS e, kinder AS k

WHERE e.nachname = k.nachname

GROUP by e.nachname, k.nachname

Ergebnis:

nachname 	Kinderanzahl 	Elternanzahl 	Taschengeld 	Anzahl_Autos

Dampf 	6 	6 	200 	9

Maier 	1 	1 	30 	1

Erwartetes Ergebnis:

nachname 	Kinderanzahl 	Elternanzahl 	Taschengeld 	Anzahl_Autos

Dampf 	3 	2 	100 	3

Maier 	1 	1 	30 	1

Ich weiss im Prinzip schon, wo das Problem liegt - nämlich, dass die Einträge alle verkreuzt verbunden werden - wie diese Ausgabe zeigt:

SELECT 

 e.nachname AS Eltern_Nachname,

 e.vorname AS Eltern_Vorname,

  k.nachname,

  k.vorname

FROM `eltern` AS e, kinder AS k

WHERE e.nachname = k.nachname

Eltern_Nachname 	Eltern_Vorname 	nachname 	vorname

Dampf 	Hans 	Dampf 	Martin

Dampf 	Helga 	Dampf 	Martin

Dampf 	Hans 	Dampf 	Katja

Dampf 	Helga 	Dampf 	Katja

Dampf 	Hans 	Dampf 	Karina

Dampf 	Helga 	Dampf 	Karina

Maier 	Sepp 	Maier 	Sepp Junior

Nur weiss ich keine Lösung wie ich an mein erwartetes Ergebnis komme. Kann mir jemand einen Tip geben, wie ich das lösen kann?

[dr.dimitri]

Ich weiss im Prinzip schon, wo das Problem liegt - nämlich, dass die Einträge alle verkreuzt verbunden werden - wie diese Ausgabe zeigt

Nein werden sie nicht. Hättest du ein Kreuzprodukt, dann dann wären es 12 Einträge. Du bekommt nur das normale Joinergebnis.

Dein Problem ist, dass Du versuchst eine objektorientiertes Modell 1:1 in ein relationales Modell umzusetzen und das ist falsch.

Für Kindern und Eltern darf es nur eine Personentabelle geben. Die Beziehungen innerhalb dieser Tabelle werden über eine eigene Beziehungstabelle abgebildet.

Die Attribute Auto und Taschengeld sind so eine Sache. Eigentlich müssten sie in eine jeweils eigene Tabelle, man kann aber auch überlegen, dass man sie in die Personentabelle mitaufnimmt solange keine zusätzlichen informationen gespeichert werden müssen (z.B. wann das Taschengeld immer ausbezahlt wird, oder welche Marke,Farbe etc. das Auto hat).

Dim

[Iomanip]

Hi Dim,

die Tabelle kann ich leider so nicht ändern. Diese sind so vorgegeben.

Das oben gezeigte soll nur das Problem illustrieren - die Tabellenstrukur ist im Orginal eine andere - gibt aber, wie gesagt, das Problem wieder.

Im Orginal sind es vom Thema her verschiedene Tabellen - also nichts was sie gemeinsam hätten. Ausser einem Datumsfeld. In meinem Beispiel stellt der Nachname die gemeinsame Verbindung, anstatt wie im Orginal das Datumsfeld.

Bearbeitet 30. Juni 2008 von Iomanip

[dr.dimitri]

Dann hast Du ein Problem, denn es fehlt die Zuordnung von Eltern zu Kind. Über den Nachnamen zu joinen ist nicht wirklich sinnvoll, denn sollte es wider erwarten zwei Elternpaar mit dem gleichen Nachnamen geben (was natürlich so nie vorkommt ) dann bekommen sie auch die Kinder des anderen Paares zugewiesen.

Du brauchst eine eindeutige Zuordnung zwischen Eltern und Kinder, voher brauchen wir mit dem weiteren SQL gar nicht anfangen (denn auch dann hast noch das Problem, dass bei einem Join Vater und Mutter jeweise alle Kinder zugewiesen bekommen).

Dim

[Tweetymax]

Also zu deinem Problem:

Mit einer einzigen Abfrage ist es wahrscheinlich nur mit nem SubSelect möglich, was MySQL meines wissens erst mit der 5'er Version unterstützt (bin mir da aber auch nicht ganz sicher)

An sonsten versuch es doch über 2 SQL-Statements zu lösen, wo du erst die Eltern, und danach die Kinder ermittelst.

Trotzdem bleibt bei dem ganzen Aufbau noch das Problem, was passiert, wenn Nachnamen bei den Eltern mehr als 2 mal auftauchen... wem gehören dann die Kinder? Bzw. gehören die Kinder wirklich den Eltern in der Eltern Tabelle?

Und was ist wenn die Kinder auch Kinder haben, also die Eltern Großeltern sind? Fragen über Fragen