MySQL 4.0.2 -> Join Troubles (Pro's gefragt)

[Mik3e]

Hi!

Hab dezente Join Troubles zwischen vier Tabellen... (wahrscheinlich steh ich auch nur auf der Leitung)

Vier Tabellen:

1. tbl_buchung
2. tbl_kunde
3. tbl_produkte
4. tbl_transaction_log

Beispieldaten:

-----------------------------------------------------------------------------
# tbl_buchung:
buchungID | buchungDatum
4711 | 01.09.2005

# tbl_kunde: 1:1 Beziehung zu tbl_buchung
kundeID | fkbuchungID | name
1 | 4711 | manfred

# tbl_produkte 1:n Beziehung zu tbl_buchung
produktID | fkbuchungID | preis
1 | 4711 | 10.00
2 | 4711 | 20.00
3 | 4711 | 25.00

# tbl_transaction_log 1:n Beziehung zu tbl_buchung
logID | fkbuchungID | aktion | bezahlartID
1 | 4711 | INITIATED | 001
2 | 4711 | RESERVED | 001
3 | 4711 | BILLED | 001
-----------------------------------------------------------------------------

Aufgabenstellung:
Wie ihr sicher erkannt habt, handelt es sich dabei um ein Bestellsystem. Die Tabelle tbl_transaction_log dient zum Loggen des Online-Payment Processes. Dieser besteht für gewöhnlich aus 2-3 Schritten (im Beispiel 3). Es soll nun ein Report generiert werden, der alle Buchungen enthält sowie die Angabe der Anzahl an Produkten und Gesamtwert der Buchung (COUNT und SUM von tbl_produkte pro Buchung).

Ich benötige jedenfalls die bezahlartID aus der Tabelle tbl_transaction_log...
Und hier ist auch schon das Problem:

Wird die tbl_transaction_log gejoint, wird die SUMME und die ANZAHL an Produkten mit der im tbl_transaction_log hinterlegten Datensatzanzahl multipliziert (hier also *3). Und das trotz INNER JOIN..

Hier der SQL QUERY:

SELECT tbl_buchung.`buchungID` AS `buchungID`
tbl_kunde.`name` AS `name`,
COUNT(tbl_produkte.`produktID`) AS `produktanzahl`,
SUM(tbl_produkte.`preis`) AS `buchung_preis` '
FROM `tbl_buchung` AS `tbl_buchung`
INNER JOIN `tbl_kunde` AS tbl_kunde ON tbl_buchung.`pk_buchung_id`=tbl_kunde.`fkbuchungID` '
INNER JOIN `tbl_produkte` AS tbl_produkte ON tbl_buchung.`pk_buchung_id`=tbl_produkte.`fkbuchungID` '
INNER JOIN `tbl_buchung_transaction_log` AS tbl_buchung_transaction_log ON tbl_buchung.`pk_buchung_id`=tbl_buchung_transaction_log.`fkbuchungID`
WHERE 1>0 '
GROUP BY tbl_buchung.`buchungID`

Dieser Query liefert folgendes (falsches) Resultat:
buchungID | name | produktanzahl | buchung_preis
-------------------------------------------------------------------------
4711 | Manfred | 9 | 165.00

Richtig wäre:
buchungID | name | produktanzahl | buchung_preis
-------------------------------------------------------------------------
4711 | Manfred | 3 | 55.00

Könnt Ihr mir einen Tipp geben, wo mein Denkfehler liegt?!
Wäre toll!

Danke & LG
Mike

 

[hpvw]

Du könntest folgendes versuchen:

SELECT 
  tbl_buchung.`buchungID` AS `buchungID`,
  tbl_kunde.`name` AS `name`,
  COUNT(tbl_produkte.`produktID`) AS `produktanzahl`,
  SUM(tbl_produkte.`preis`) AS `buchung_preis`
FROM `tbl_buchung` AS `tbl_buchung`
INNER JOIN `tbl_kunde` AS tbl_kunde 
  ON tbl_buchung.`pk_buchung_id`
      =tbl_kunde.`fkbuchungID` '
INNER JOIN `tbl_produkte` AS tbl_produkte 
  ON tbl_buchung.`pk_buchung_id`
      =tbl_produkte.`fkbuchungID` '
INNER JOIN `tbl_buchung_transaction_log` AS tbl_buchung_transaction_log 
  ON tbl_buchung.`pk_buchung_id`
      =tbl_buchung_transaction_log.`fkbuchungID`
LEFT JOIN `tbl_buchung_transaction_log` AS tbl_buchung_transaction_log2 
  ON tbl_buchung_transaction_log2.`fkbuchungID`
      >tbl_buchung_transaction_log.`fkbuchungID`
    AND tbl_buchung.`pk_buchung_id`
      =tbl_buchung_transaction_log2.`fkbuchungID`
WHERE tbl_buchung_transaction_log2.`fkbuchungID` IS NULL
GROUP BY tbl_buchung.`buchungID`

Dein WHERE war ja ziemlich überflüssig
Nun hat es einen Sinn.
Die Log-Tabelle wird mit sich selbst per LEFT JOIN verknüpft. Es werden jedoch nur die Zeilen übernommen, in denen die ID der linken Seite größer ist, als die der rechten Seite, durch LEFT JOIN werden bei der größten ID die Werte der rechten Tabelle null. Übrig bleibt Folgendes:

Left Right
   1    2
   1    3
   2    3
   3    null

Mit WHERE ... IS NULL bleibt nur die letzte Zeile übrig, theoretisch zumindest.

Deine 1:1-Beziehung zum Kunden ist mir übrigends noch nicht ganz klar. Können Deine Kunden nur ein mal bestellen? Ich würde eher bei der Buchung die KundeID speichern oder halt die Kundendaten direkt bei der Buchung ablegen, wenn Du Dir mit der 1:1-Beziehung sicher bist.

Gruß hpvw

 

[Mik3e]

Hi... Im Prinzip bin ich zur selben Lösung gekommen.
Vorweg: Der Where Teil ist in der "echten" Abfrage recht umfangreich (habe ich nur hier durch nen Platzhalter ersetzt).

Wie gesagt bin ich auf der Leitung gestanden, denn im Transaction Log kann zwar jeder Datensatz mehrmals vorkommen (zu einer Buchung), allerdings immer mit einem anderen Status (INITIATED, RESERVED, BILLED oder ERROR).

Ich habe nun im Join einfach folgendes eingebaut:

. ' LEFT JOIN `tbl_buchung_transaction_log` AS t7 ON t1.`pk_buchung_id`=t7.`fk_buchung_id` AND t7.`buchung_transaction_log_transaction_bezeichnung`=\'INITIATED\' '

Damit liefert mir dieser LEFT-Join nur noch einen Datensatz (was natürlich auch sinn macht) und das Problem ist damit gelöst

Danke trotzdem,
LG
Mike

 

[Mik3e]

Verdammt, dass war es doch nicht ganz
Denn es gibt noch weitere Tabelle, die mehrere Datensätze liefern... Die brauche ich aber unbedingt für die Abfrage... Keine Ahnung, wie ich das mit dem COUNT bzw. SUM hinbekommen kann, ohne dafür eine eigene Abfrage zu machen.

Zu den Kundendaten:
Natürlich kann jeder Kunde mehrfach bestellen.. Theoretisch könnte man die Kundendaten also auch direkt bei der Buchung speichern. Dieses Konstrukt ist nur so aufbereitet, damit ein (späterer) Umstieg auf eine registrierte Variante möglich ist (Kunde muss/kann sich registrieren, damit er bestellen kann).

Ciao,
Mike