2 Counts aus zwei verschiedenen Tabellen

[michael97]

Hallo und Willkommen,
ich habe eine Abfrage, bei der ich schon seit langer Zeit hänge.
Ich habe 3 Tabellen mit den Namen "waren", "rechnungen" und "srechnungen".
Die Tabellen sind wie folgt aufgebaut:
waren

|    id     |     schluessel     |

rechnungen

|    id     |     waren_id     |

srechnungen

|    id     |     rechnungen_id     |

In der Tabelle "waren" stehen Produkte mit einer id und einem einmaligen Schlüssel. Wenn diese verkauft werden wird in die Tabelle "rechnungen" ein neuer Eintrag erstellt, der als waren_id die id der Ware erhält.
Wird die Rechnung storniert, kommt in die Tabelle "srechnungen" ein Eintrag mit der id der entsprechenden Rechnung aus der Tabelle "rechnungen".
Zu erwähnen ist noch, dass ein verkaufter Artikel nach erfolgter Stornierung erneut verkauft werden und wieder storniert werden kann und das unbegrenzt oft.

Mein Ziel ist es jetzt, dass wenn ich nach einem Warenschlüssel suche, mir alle Rechungen zu der Ware und zusätzlich alle Stornorechnungen, sowie die Anzahl der Rechnungen und Stornorechnungen angezeigt werden.

Mein bisheriger Ansatz zur Bestimmung der Anzahl der Rechnungen und Stornorechnungen zu einem Artikel lautet:

SELECT
    wid
    (SELECT COUNT(rechnungen.id) FROM rechnungen WHERE waren_id = w.wid) anzahlrechnungen
    (SELECT COUNT(stornorechnungen.id) FROM srechnungen WHERE rechnungs_id = rechnungen.id
FROM
    (SELECT
        id AS wid
    FROM
       waren
    WHERE
        schluessel = 69
    )
AS w
LEFT JOIN rechnungen ON (w.wid = rechnungen.waren_id)

Der Code liefert auch fast das richtige Ergebnis. Angezeigt bekomme ich so etwas:

Array
(
[wid] => 8
[schluessel] = 69
[anzahlrechnungen] => 2
[anzahlstornorechnungen] => 1
)

Array
(
[wid] => 8
[schluessel] = 69
[anzahlrechnungen] => 2
[anzahlstornorechnungen] => 1
)

Allerdings existieren zu dem Artiel 2 Stornorechnungen und er sollte mir [anzahlstornorechnungen] = 2 anzeigen. Auch ein GROUP BY wid ändert an der Sache nichts. So verschwindet nur ein Array-Element und das übriggebliebende behält weiterhin [anzahlstornorechnungen] = 1.

Bin über jede Hilfe dankbar

Viele Grüße,
Michael

 

[Distrilec]

Auch ein GROUP BY wid ändert an der Sache nichts

Stimmt... Dein Select ist nämlich einfach falsch
Und MySQL wäre nunmal nicht MySQL wenn es solche Fehler tatsächlich auch erkennen würde, anstatt einfach ein "zufälliges" Ergebnis zu liefern...

Das dürfte dir weiterhelfen

Select w.id
      ,Count(distinct r.id) as anzahl_rechnungen
      ,Count(distinct s.id) as anzahl_storniert
From   waren w

Left   Join rechnungen r
On     r.waren_id = w.id

Left   Join srechnungen s
On     s.rechnungen_id = r.id

Where  w.schluessel = 'xyz'

Group  By w.id

Der mögliche Fall von Null-Zeilen werde ich dir nicht rausfiltern... Das ist mir ehrlich gesagt zu viel Aufwand... Da wäre es wesentlich sinnvoller dein "Modell" zu überarbeiten...

 

[michael97]

Hallo Distrilec,
vielen Dank für deine Antwort. Es klappt jetzt super und macht genau was es soll. Vielen vielen Dank.

Da ich mich erst seit einigen Tagen mit Datenbanken im Allgemeinen beschäftige würde mich interessieren, was du meinst mit "Modell überarbeiten"?
Ist diese Lösung nicht sonderlich performant oder nicht elegant oder einfach schlecht?

Viele Grüße,
Michael

 

[Distrilec]

Ist diese Lösung nicht sonderlich performant oder nicht elegant oder einfach schlecht?

Ja, ja und ja.
Es wäre wesentlich sinnvoller der Rechnung einen Status ("Gedruckt", "Fakturiert", "Storniert", etc...) zu geben, als einzelne Zustände in eigene Tabellen auszulagern.

 

[michael97]

Ja, ja und ja.
Es wäre wesentlich sinnvoller der Rechnung einen Status ("Gedruckt", "Fakturiert", "Storniert", etc...) zu geben, als einzelne Zustände in eigene Tabellen auszulagern.

Oh man, das naheliegendste wird meistens übersehen. Das vereinfacht Vieles natürlich ungemein. Da lohnt sich der Umbau des Programmes ja noch mal.
Hier tun sich mir weitere Fragen auf. Eine Stornierung kann es zu jeder Rechnung ja nur einmal geben. Aber Gutschriften können ja mehrere zu einer Rechnung gehören. Wie sollte die Struktur da aussehen? Sollte ich hier eine weitere Tabelle anlegen in der ich die Gutschriften speicher?
Und falls ich das Datum der Stornierung und den grund mit abspeichern möchte, wie löse ich das dann? Ist es hier aus sinnvoll mehrere Felder in der Tabelle rechnungen zu haben und diese nur bei Gebrauch zu befüllen?

Viele Grüße

 

[Distrilec]

Die Frage ist hier eher wie du Gutschriften betrachtest und spätestens hier bin ich dann aber leider auch raus... Finanzbuchhaltung ist überhaupt nicht meine Welt

 

[akretschmer]

Aber Gutschriften können ja mehrere zu einer Rechnung gehören. Wie sollte die Struktur da aussehen? Sollte ich hier eine weitere Tabelle anlegen in der ich die Gutschriften speicher?

Ja.

 

[michael97]

Vielen Dank euch. Ihr habt mir sehr geholfen. Jetzt habe ich nur noch die eine offene Frage wegen der zusätzlichen Informationen zur Stornierung. Ich lege jetzt in der Tabelle rechnungen eine weitere Spalte "stornierung" an, die entweder die Werte 0 oder 1 besitzt. Wie kann ich jetzt am besten die weiteren Daten wie "Grund der Stornierung", "Datum der Erstellung der Stornorechnung", "Mitarbeiter", ... speichern. Sollte cih dazu in der Tabelle rechnungen zusätzliche Felder anlegen oder eine neue Tabelle "stornorechnungen", die als verknüpfendes Element zur Rechnung die RechnungsID enthält?

 

[ukulele]

Wenn du eine zusätzliche Tabelle für Stornogrund etc. anlegst dann könntest du genauso den Zustand storniert über diese Tabelle abbilden und wärst wieder bei deiner Ausgangssituation. Wenn man also der Logik von Distrilec folgt (was durchaus sinnvoll sein kann), dann würde ich konsequent alle Storno-Informationen in der Rechnungstabelle ablegen.

 

[michael97]

Hallo,
das Umschreiben des Programms hat super funktioniert und die weiteren Abfragen waren wesentlich einfacher umzusetzen. Allerdings habe ich jetzt in der Tabelle rechnungen zwei kuerzel-Felder: kuerzel_rechnung und kuerzel_storno. Die Kürzel werden nicht direkt gespeichert, sondern nur die ID des jeweiligen Kürzels. Dies ist nötig, da sich ein Kürzel mal ändern kann.
Vorher hatte ich nur ein Kürzel in der Tabelle und die Bestimmung des Kürzels ging daher einfach über ein JOIN

SELECT
     kuerzel.kuerzel
FROM
    rechnungen
JOIN kuerzel ON rechnungen.kuerzel_rechnung = kuerzel.id
WHERE
    rechnungen.id = 15

Ein Umschreiben des Codes gelingt mir leider nicht. Ich weiß nicht, wo und wie ich die zweite Abfrage bzgl. kuerzel_storno einbauen muss.
Wäre nett, wenn mir jemand einen Tipp geben kann.

Viele Grüße,
Michael

 

[akretschmer]

doppelt joinen, den gejointen kuerzel-Tabellen aliase geben, z.B. kuerzel_rechnung und kuerzel_storno, und im Select-Teil dann auf kuerzel_rechnung.kuerzel zugreifen.

 

[michael97]

Hallo akretschmer,
vielen Dank für deine Antwort. Mit deiner Hilfe habe ich es hinbekommen.

Viele Grüße,
Michael

 

[ukulele]

Also quasi so:

SELECT
     k_rechnung.kuerzel AS kuerzel_rechnung,
     k_storno.kuerzel AS kuerzel_storno
FROM
    rechnungen
LEFT JOIN kuerzel k_rechnung ON rechnungen.kuerzel_rechnung = k_rechnung.id
LEFT JOIN kuerzel k_storno ON rechnungen.kuerzel_storno = k_storno.id
WHERE
    rechnungen.id = 15

Ich würde ausserdem eher LEFT JOIN verwenden, sonst wird ein Datensatzz, zu dem kein Kürzel existiert, gar nicht erst angezeigt. Auch wenn das vieleicht nicht vorkommt...