3 Tabellen verbinden

frank11

Benutzer
Beiträge
6
Hallo allerseits,

seit Tagen versuche ich ein Problem zu lösen und verzweifle allmählich daran.
Wahrscheinlich übersteigt das meinen intellektuellen Horizont.
Ihr hier seid meine letzte Hoffnung!

Es geht um eine Fahrzeug-Datenbank in MySQL und php.

Ich habe drei Tabellen:
Stammdaten
km
Werkstattfahrten

Die Datensätze in den Tabellen sollen über das Feld Kennzeichen (der Fahrzeuge) verbunden werden.
Das Feld Kennzeichen ist in allen drei Tabellen vorhanden.

In Tabelle Stammdaten ist für jedes Kennzeichen nur 1 Datensatz vorhanden.
ABER:
in Tabelle km sind für jedes Kennzeichen mehrere Datensätze vorhanden.
Auch in Tabelle Werkstattfahrten sind für jedes Kennzeichen mehrere Datensätze vorhanden.

Ich möchte nun für jedes einzelne Kennzeichen die gefahrenen Kilometer ermitteln.
Das funktioniert tadellos.

Weiter möchte ich für jedes einzelne Kennzeichen die Summe der Rechnungsbeträge aus der Tabelle Werkstattfahrten ermitteln. Und das klappt nicht. Die Summe der Rechnungsbeträge wird nicht einmal ermittelt, sondern so oft, wie in der Tabelle km (!) Einträge für das jeweilige Fahrzeug vorhanden sind. Wenn ich also in der Tabelle km 3 Einträge für das jeweilge Kennzeichen habe, ist der Rechnungsbetrag der Werkstattfahrten 3 x so hoch wie er sein sollte.

Kann mir hierbei jemand helfen?
Ich wäre sehr dankbar!

Hier noch Auszüge aus den Tabellen:

Tabelle Stammdaten
Kennzeichen | Fahrzeugtyp
DE-MO 34 | Ford Ranger
DE-MO 437 | Unimog 405/10

Tabelle km
Kennzeichen | Monat | km-Stand
DE-MO 34 | 2016-01-22 | 190902
DE-MO 34 | 2016-02-18 | 191430
DE-MO 34 | 2016-03-15 | 191820
DE-MO 437 | 2016-01-17 | 42850
DE-MO 437 | 2016-02-10 | 43912

Tabelle Werkstattfahrten
Kennzeichen | Datum | Rechnungsbetrag
DE-MO 34 | 2016-02-12 | 465
DE-MO 34 | 2016-02-25 | 112
DE-MO 437 | 2016-01-13 | 67
DE-MO 437 | 2016-01-15 | 53
DE-MO 437 | 2016-02-03 | 185
DE-MO 437 | 2016-03-18 | 246

gewünschtes Ergebnis
Kennzeichen | gefahrene km | Kosten
DE-MO 34 | 918 | 577
DE-MO 437 | 1062 | 551

Und das kriege ich nicht hin, ich sitze seit Tage daran!

Schon mal vielen Dank im voraus!

Gruß

Frank
 
Werbung:
Ja, ich habe schon -zig Abfragen erstellt.

Im folgenden einer der Abfagen. Diese enthält noch zusätzliche Daten, die ich oben nicht genannt hatte, und die meiner Meinung nach keine Rolle spielen.


SELECT distinct a.Kennzeichen,
a.Kunde,
a.Kunde_BS,
a.FahrzeugTyp,
a.Aufnahme_DB,
a.Abmeldung_DB,

c.Kennzeichen,
(Max(c.km_Stand)-Min(c.km_Stand)) AS km_gef,

d.Kennzeichen,
sum(d.Rechnungsbetrag) AS Kosten

FROM
FD_FZDB_Stammdaten AS a
INNER JOIN FD_FZDB_km AS c
INNER JOIN FD_FZDB_Werkstattfahrten AS d
ON a.Kennzeichen = c.Kennzeichen AND
a.Kennzeichen = d.Kennzeichen

WHERE a.Kennzeichen LIKE '%DE-MO%' AND
a.Aufnahme_DB != '0000-00-00' AND
a.Abmeldung_DB = '0000-00-00'
GROUP BY a.Kennzeichen
ORDER BY $ordnen"

Diese Abfrage funktioniert (bringt keine Fehlermeldung), aber die Ergebnisse sind z. T. falsch.
Die gefahrenen Kilometer (km_gef) werden korrekt berechnet.
Die Summe der Rechnungsbeträge (Kosten) wird nicht korrekt berechnet. Die Rechnungsbeträge werden offenbar pro Fahrzeug so oft summiert, wie für das jeweilige Fahrzeug km-Stände vorhanden sind. und das ist natürlich falsch.

Ich werde noch irre!
 
Du begehst einen klassischen Fehler den MySQL leider nicht korrekt mit einer Fehlermeldung ahndet sondern einfach irgendein Ergebnis hin wurstet.

Grundsatz: Bei der Verwendung von GROUP BY muss jede Spalte, die im SELECT-Teil ausgegeben wird, entweder gruppiert oder aggregiert werden.

Alles was in Tabelle a steht kannst du problemlos in das GROUP BY aufnehmen. c.Kennzeichen und d.Kennzeichen macht eigentlich keinen Sinn weil es ja gleich a.Kennzeichen ist, könnte aber auch in den GROUP BY. Warscheinlich hast du noch weitere Spalten aus d in deinem Select die nicht aggregiert oder gruppiert werden?
 
Code:
test=*# select * from stammdaten ;
  kz  |  typ   
-----------+--------
 DE-MO 34  | Ford
 DE-MO 437 | Unimog
(2 rows)

test=*# select * from km ;
  kz  |  datum  |  km   
-----------+------------+--------
 DE-MO 34  | 2016-01-22 | 190902
 DE-MO 34  | 2016-02-18 | 191430
 DE-MO 34  | 2016-03-15 | 191820
 DE-MO 437 | 2016-01-17 |  42850
 DE-MO 437 | 2016-02-10 |  43912
(5 rows)

test=*# select * from ws_fahrten ;
  kz  |  datum  | betrag
-----------+------------+--------
 DE-MO 34  | 2016-02-12 |  465
 DE-MO 34  | 2016-02-25 |  112
 DE-MO 437 | 2016-01-13 |  67
 DE-MO 437 | 2016-01-15 |  53
 DE-MO 437 | 2016-02-02 |  185
 DE-MO 437 | 2016-02-18 |  246
(6 rows)

test=*# select s.kz, bar.sum as km_gefahren, ws.sum as kosten from stammdaten s left join (select kz, sum(x) as sum from (select kz, lead(km) over (partition by kz order by datum) - km  as x from km) foo group by kz) bar  on s.kz=bar.kz left join (select kz, sum(betrag) from ws_fahrten group by kz) ws on s.kz=ws.kz;
  kz  | km_gefahren | kosten
-----------+-------------+--------
 DE-MO 34  |  918 |  577
 DE-MO 437 |  1062 |  551
(2 rows)

Ist aber mit PostgreSQL gemacht, die Berechnung der km-Differenzen geht halt über Window-Funktionen so recht einfach.
 
Erst mal danke Euch beiden, ukulele und akretschmer.
Ich denke, Eure Ansätze helfen mir weiter ... ich arbeite dran und bin einen Schritt weiter gekommen.
Vielen Dank!

@akretschmer:
Mein Problem ist - das hatte ich oben vergessen zu schreiben - dass ich das nicht mit drei einzelnen Abfragen realisieren möchte, sondern mit einer einzigen. Der Grund dafür ist, dass ich in der Ergebnistabelle auch nach den einzelnen Ergebnissen (Kennzeichen, gefahrene km, Kosten) ordnen können möchte.

Im Moment bin ich anscheinend einer Lösung ziemlich nahe - möchte Euch daher sagen, dass Ihr momentan nicht weiter darüber nachzudenken braucht. Kann aber sein, dass ich das wieder zurücknehmen muss.;)

Wenn meine Versuche klappen, werde ich das Ergebnis hier reinstellen - wenn nicht, werde ich mich noch mal mit Fragen melden.
 
Also etwas runter gekürzt müsste deine Lösung ja schon was liefern:
Code:
SELECT   a.Kennzeichen,
     a.Kunde,
     a.Kunde_BS,
     a.FahrzeugTyp,
     a.Aufnahme_DB,
     a.Abmeldung_DB,
     max(c.km_Stand)-min(c.km_Stand) AS km_gef,
     sum(d.Rechnungsbetrag) AS Kosten
FROM   FD_FZDB_Stammdaten AS a
INNER JOIN FD_FZDB_km AS c
ON     a.Kennzeichen = c.Kennzeichen
INNER JOIN FD_FZDB_Werkstattfahrten AS d
ON     a.Kennzeichen = d.Kennzeichen
WHERE   a.Kennzeichen LIKE '%DE-MO%'
AND     a.Aufnahme_DB != '0000-00-00'
AND     a.Abmeldung_DB = '0000-00-00'
GROUP BY a.Kennzeichen,
     a.Kunde,
     a.Kunde_BS,
     a.FahrzeugTyp,
     a.Aufnahme_DB,
     a.Abmeldung_DB
 
Werbung:
Ich danke Dir für die Mühe, ukulele.
Aber Dein Vorschlag liefert eben den Effekt, der mir Probleme bereitet hat.

Real ist 'sum(d.Rechnungsbetrag) AS Kosten' für ein bestimmtes Fahrzeug = 791 EUR.
Deine Lösung spuckt für dieses Fahrzeug 13.447 EUR aus, also genau das 17-fache.

Aber ich hab das jetzt nach dem Vorschlag gemacht, denn Du hier vorgeschlagen hast. Sieht fast so aus, dass das klappen könnte!
Also sei Dir gedankt für jenen anderen Post!

@akretschmer:
Ich hab Deinen Vorschlag nicht verstanden, kenne mich mit PostgreSQL aber auch überhaupt nicht aus.
Aber danke auch an Dich!
 
Zurück
Oben