komplizierte Join-Abfrage mit rechenoperatoren

[hermesias]

Guten Abend ins Forum! Ich (Florian) bin erst gerade offiziell dazu gestoßen. Bislang habe ich immer alles in Beträgen gefunden und konnte so meinen Code entwickeln, aber nun wird meine Problemstellung doch zu komplex und ich muss euch Experten fragen:

Meine Datenbank hat folgende Tabellen:

• TBL_kunden
• TBL_rechnungen
• TBL_artikel
• TBL_warenkorb

Den einzelnen Rechnungen werden mittels Kundennummer 'k_id' Kunden zugeordnet. Anhand von Positionen in der Tabelle Warenkorb wird ersichtlich in welcher Rechnung (r_id) welche Menge (menge) der einzelnen Artikel (a_id) aus der Tabelle TBL_artikel gekauft wird. In der Tabelle TBL_artikel ist hinterlegt, welcher Artikel zu welchem Preis angeboten wird.

Auflistung der Rechnungen anhand r_id:

• Ermittle in der Tabelle 'TBL_kunden' zu wem die eingetragene Kundennummer (k_id) gehört.
• Wähle aus der Tabelle 'TBL_warenkorb' alle Positionen aus, die die gleiche Rechnungs-id 'r_id' haben.
• Suche nun anhand der Artikel-id der Positionen in der Tabelle 'TBL_artikel' den jeweiligen Preis 'preis' aus.
• multipliziere ihn mit der angeforderten Menge 'menge' der Artikel in der Tabelle TBL_warenkorb.
• Summiere diese einzelnen Positionen zu einem Zwischenergebnis, dem noch das veranschlagte Porto aus der TBL_rechnungen hinzugefügt wird.

Gib die Liste aller Rechnungen als Tabelle per php script aus, - mit den Spalten: r_id, kunde, rechnungsdatum, gesamtbetrag.

Geht das überhaupt über eine JOIN Abfrage mit Rechenoperatoren oder muss ich das irgendwie anders lösen?

freue mich sehr über Antworten

[Rolf B]

Hallo hermesias,

das geht definitiv, wird aber ein ziemlich fettes SQL Statement. Guck ich mir nachher mal an.

Mich irritiert nur, dass da ein paar Vereinfachungen drin sind, die die Aufgabe praxisfremd machen. Z.b. das getrennte Ausweisen der Mehrwertsteuer oder Versandkosten, die ab einer bestimmten Rechnungssumme entfallen.

Angesichts der hohen Lehrerdichte im Selfhtml-Forum gibt es die Übereinkunft, anderen nicht die Hausaufgaben zu machen. Auch und erst recht nicht für Fachinformatiker. In Schule oder Uni ist das eigentlich zu komplex für eine Hausaufgabe. Hm…

Rolf

[Robert B.]

Moin Florian,

Meine Datenbank hat folgende Tabellen:

• TBL_kunden
• TBL_rechnungen
• TBL_artikel
• TBL_warenkorb

Den einzelnen Rechnungen werden mittels Kundennummer 'k_id' Kunden zugeordnet. Anhand von Positionen in der Tabelle Warenkorb wird ersichtlich in welcher Rechnung (r_id) welche Menge (menge) der einzelnen Artikel (a_id) aus der Tabelle TBL_artikel gekauft wird. In der Tabelle TBL_artikel ist hinterlegt, welcher Artikel zu welchem Preis angeboten wird.

Wenn der Preis nachträglich geändert wird, ändert sich demnach auch die Rechnung – nachträglich! In einem statischen Beispiel kann man das ignorieren, in einer echten Anwendung muss das im Design berücksichtigt werden.

Auflistung der Rechnungen anhand r_id:

• Ermittle in der Tabelle 'TBL_kunden' zu wem die eingetragene Kundennummer (k_id) gehört.
• Wähle aus der Tabelle 'TBL_warenkorb' alle Positionen aus, die die gleiche Rechnungs-id 'r_id' haben.
• Suche nun anhand der Artikel-id der Positionen in der Tabelle 'TBL_artikel' den jeweiligen Preis 'preis' aus.
• multipliziere ihn mit der angeforderten Menge 'menge' der Artikel in der Tabelle TBL_warenkorb.
• Summiere diese einzelnen Positionen zu einem Zwischenergebnis, dem noch das veranschlagte Porto aus der TBL_rechnungen hinzugefügt wird.

Gib die Liste aller Rechnungen als Tabelle per php script aus, - mit den Spalten: r_id, kunde, rechnungsdatum, gesamtbetrag.

Das liest sich wie eine (von außen) vorgegebene Aufgabenstellung, eventuell eine Hausaufgabe, wie Rolf vermutet. Das Motto von SELFHTML ist Die Energie des Verstehens, daher werden im Forum Anregungen und Hilfestellungen gegeben, aber keine Aufgaben gelöst.

Was in Deinem Fall vielleicht hilft, ist sich die Tabellen und ihre Beziehungen einmal aufzuzeichnen. Anschließend überlegst Du Dir wie die entsprechenden SELECT-Statements für die oben genannten Teilabfragen aussehen. Von dort aus kannst Du die Statements und Ergebnismengen nach und nach zusammenführen („joinen“).

Geht das überhaupt über eine JOIN Abfrage mit Rechenoperatoren oder muss ich das irgendwie anders lösen?

SQL bietet die Standard-Rechenoperationen, die dafür nötig sind, z.B.

-- Füge Umsatzsteuer hinzu:
SELECT preis * 1.19 AS brutto_preis FROM TBL_artikel /* WHERE … */;

-- Ziehe festen Rabatt von 1 € (entsprechend 100 Cent) ab:
SELECT preis - 100 AS angebots_preis FROM TBL_artikel /* WHERE … */;

-- Oder: Rabatt auf den Bruttopreis:
SELECT preis * 1.19 - 100 AS brutto_preis FROM TBL_artikel /* WHERE … */;

-- bzw. Rabatt auf den Nettopreis:
SELECT (preis - 100) * 1.19 AS brutto_preis FROM TBL_artikel /* WHERE … */;

Wahrscheinlich sind auch Aggregatfunktionen wie SUM für Dich interessant.

Viele Grüße
Robert

[hermesias]

Hallo, Guten Morgen.

Vielen Dank für eure Antworten! Wie schön, dass ihr euch meinem Anliegen angenommen habt! Lustig, dass es wie eine Hausaufgabe rüberkommt.😂 Ich hatte versucht die Fragestellung für mich und euch verständlich zu machen und bin dabei wohl in einen Schulbuchjargon verfallen sorry!

Die Buchhaltungsanwendung mache ich gerade für einen sehr kleinen fast privaten Verlag. Sie wird dringend gebraucht und die Artikel sind alles Bücher.

Verpackung und Versand wird in der "Rechnung-erstellen-Maske individuell eingegeben, kann also auch 0,00 sein. Mwst ist immer 7%.

Aber der Hinweis, dass sich Preise ja ändern können (obwohl das bei dem Verlag eigentlich nie vorkommt (Buchpreisbindung)) ist doch etwas was dann in Zukunft zu gravierenden Fehlern führen könnte. (danke Robert für diesen wichtigen Hinweis!)

Ich werde wohl doch eine Spalte 'gesamtpreis' in der TBL_rechnungen hinzufügen müssen, in der ich bei der Erstellung die mit php ermittelte Summe eintrage. Die Rechnungen werden via fpdf generiert und ändern sich nicht mehr aber in der Suche nach den Rechnungen wurde gewünscht sie nicht nur anhand der ReNr in der Tabelle identifizieren zu können sondern auch anhand des Rechnungsbetrages.

Super auch die Beispiele der Rechenoperatoren! ich werde sie sicher noch für die weitere Arbeit (Kundenkonto, Bestandliste der Bücher usw.) brauchen.

Vielen Dank nochmal für eure Mühe! Florian

[Rolf B]

Hallo hermesias,

Die Buchhaltungsanwendung mache ich gerade für einen sehr kleinen fast privaten Verlag.

Hoffentlich kennst Du alle rechtlichen Vorschriften, die dafür zu beachten sind. Wenn Du eine nicht beachtest, hast Du den Dreck am Hacken.

Ich werde wohl doch eine Spalte 'gesamtpreis' in der TBL_rechnungen

Kannst Du (aus anderen Gründen) machen, ist aber buchhalterisch unzureichend. Ein Warenkorb-Eintrag ist Grundlage für einen Buchungsbeleg, und deshalb musst Du den angesetzten Einzelpreis an den Positionen im Warenkorb speichern, würde ich mal behaupten. Ob das fachlich richtig ist, weiß ich nicht. Szenario: Kunde A legt Artikel X in den Warenkorb. Preis ist 12€. Kunde unterbricht, kommt später wieder, der Preis ist mittlerweile geändert worden auf 13€. Soll der Kunde beim Kauf 12€ oder 13€ vorfinden? Es ist ja noch ein Warenkorb und nicht die Rechnung, d.h. NOCH muss der Preis nicht fixiert sein.

Als Softwareentwickler mit fast 40 Jahren Berufserfahrung frage ich dich: Bist Du sicher, dass deine Fachkompetenz (auf Business-Seite, nicht auf Informatik-Seite) für eine Faktura ausreichend ist? Bist Du sicher, dass Du das billiger bauen und pflegen kannst als eine fertige Faktura zu kaufen bzw. als Cloudservice zu buchen? Oder ist das eine Legacy-Buchhaltung, die Du "geerbt" hast und am Laufen halten musst? In dem Fall prüfe die Idee einer Migration auf Standardsoftware. Cloud hat den Vorteil, dass die Datensicherung nicht mehr Dein Problem ist (oder nur dann, wenn der Cloudanbieter Scheiße baut).

Eine Spalte "Gesamtpreis" auf den Rechnungen wäre redundant - aber je nach Aufgabenstellung und Systembelastung wäre das eine brauchbare Performanceoptimierung. „Redundanz ist das Schmierfett einer Datenbank“, heißt es, und dementsprechend macht man sich damit die normalisierten Finger schmutzig.

Eine Alternative könnte sein, die Preise aus der Artikeltabelle auszulagern und je Artikel den gültigen Preis mit dem Zeitraum zu speichern, in dem er gilt. Das macht das SQL nochmal komplizierter, und es ist die Frage, ob irgendwer die historische Preisentwicklung nachhalten möchte. Ich glaube, den angesetzten Preis im Warenkorbposten zu speichern wäre besser. Es könnte ja auch Rabattaktionen geben, nicht jeder Artikel im Buchladen unterliegt der Preisbindung. Bzw. man kann sie mit Remittenden unterlaufen.

Was auch eine Frage wäre: Ist es sinnvoll, Rechnungsposten und Warenkorbeinträge gleichzusetzen? Rechnungsposten sind Buchhaltungsdaten, die per Definition unveränderlich sein müssen. Der Warenkorb sind operative Daten, die sich im Bestellprozess ständig ändern. Das sind zwei verschiedene Nutzungsprofile, insbesonderen haben sie unterschiedliche Anforderungen an Aufbewahrungsfristen und Datensicherung. Es wäre wohl eine gute Idee, nach abgeschlossenem Kauf die Zeilen aus dem Warenkorb in eine andere Tabelle zu verschieben, wo nur Rechnungsposten drin sind. Damit belasten sich das Bestellwesen und die Faktura nicht gegenseitig.

Aber bleiben wir erstmal dabei, dass das eine Tabelle ist.

Für deine gesuchte Query hat der Preis auf den Warenkorbeinträgen bzw. Rechnungsposten den Vorteil, dass Du einen Join weniger hast. Du hast nur noch Kunden --(1:n)-> Rechnungen --(1:n)-> Rechnungsposten. Das ist ein relativ einfacher Join.

Den Gesamtpreis für EINE Rechnungs-ID bekommst Du, wenn der Preis in die Warenkorbzeilen eingetragen wird, so:

SELECT SUM(w.menge*w.preis)
FROM  warenkorb w
WHERE w.r_id = ****

**** ist die Rechnungs-ID, für die der Preis bestimmt werden soll.

Diese Query setzt Du als Subselect in die Hauptquery ein:

SELECT kundendaten,
       rechnungsdaten,
       (SELECT SUM(w.menge*w.preis)
        FROM  warenkorb w
        WHERE w.r_id = r.r_id) gesamtpreis
FROM tbl_kunden k,
     JOIN tbl_rechnungen r ON k.k_id = r.k_id

und bekommst damit eine Spalte "gesamtpreis".

Man kann sowas auch per JOIN lösen, aber dann müsstest Du GROUP BY verwenden und dann muss jede nichtsummierte Spalte im GROUP BY aufgelistet werden.

Rolf

[hermesias]

Ganz herzlichen Dank für die ausführliche Antwort.

Das System habe ich aus einer uralten Filmakerdatenbank geerbt. Die Kunden sind hauptsächlich Buchhandlungen und bestellen per email oder Telefon. Die Bestellungen werden dann vom Verleger in die Datenbank eingepflegt. Heraus kommen Rechnungen und Adressaufkleber als PDF. Es ist also eigentlich nicht so umfangreich.

Allerdings hast du recht, wenn du die Frage aufwirfst warum nicht gleich eine fertige Buchhaltungssoftware einsetzen.

Ich muss mir das jetzt erst mal genau überlegen, obwohl es meine ganze bisherige Arbeit zunichte machen würde ist vielleicht ein "Neustart" mit einem neuen System die bessere Lösung als mühsam die alte Datenbank aufzupäppeln.

Eure umfangreiche Unterstützung hat mich total gefreut!

vielen Dank euch Florian