Lukas: mysql5: Query gesucht (JOIN)

Hi,

ich habe eine Tabelle mit Rechnungsbeträgen. In derselben Tabelle sind auch Gutschriften und Stornos enthalten.

Der Aufbau ist in etwas so:

RGID, RGnummer, Betrag, ReferenzID, Art

Rechnungen sind so eingetragen:
1,RG0001,120,0,RG
2,RG0002,80,0,RG
3,RG0003,100,0,RG
usw.

Gutschriften stehen so drin:
3,GU0001,50,2,GU
4,GU0002,10,2,GU

Stornos stehe so drin:
5,ST0001,-120,1,ST

was bedeutet, daß die GU0001 eine Teilgtschrift der Rechnung RG0002 in Höhe von 50 Euro ist und die GU0002 eine weitere Gutschrift zur RG0002 in Höhe von 10 Euro ist.
Der Storno wäre hier der Storno zur RG0001 in voller Höhe.

Meine Frage: Ich würde gerne alle Rechnungssummen aufführen, das alleine ist kein Problem. Aber mir macht es ein Problem, die Rechnungsrestsummen aufzuführen, jedenfalls wenn ich nur 1 Query benutzen will. Hierbei macht mir der Abzug der Summe der Gutschriften das Problem.

Ziel ist also eine Auflistung wie:

RG0001 - 0 Euro
RG0002 - 20 Euro
RG0003 - 100 Euro

Wie joine ich das hier, um in einer Query die verbleibende Zahlungssumme zu ermitteln?

Lukas

  1. Lieber Lukas,

    vielleicht suchst Du den Selfjoin?

    1,RG0001,120,0,RG
    2,RG0002,80,0,RG
    3,RG0003,100,0,RG[...]
    3,GU0001,50,2,GU
    4,GU0002,10,2,GU[...]
    5,ST0001,-120,1,ST

    Wozu benötigst Du die Doppelung von Art (letzte Spalte) und dem Präfix in der Rechnungsnummer? Dass hier zweimal dieselbe ID (3) verwendet wurde, ist sicherlich ein Versehen, da die Daten nicht "echt" sind.

    Liebe Grüße,

    Felix Riesterer.

    --
    "Wäre die EU ein Staat, der die Aufnahme in die EU beantragen würde, müsste der Antrag zurückgewiesen werden - aus Mangel an demokratischer Substanz." (Martin Schulz, Präsident des EU-Parlamentes)
    1. Hi Felix,

      vielleicht suchst Du den Selfjoin?

      Ja, den suche ich. Bzw., ich wußte schon, daß ich den brauchen würde, kriegs aber dennoch nicht hin.

      Wozu benötigst Du die Doppelung von Art (letzte Spalte) und dem Präfix in der Rechnungsnummer?

      Wäre in der Tat nicht nötig.

      Dass hier zweimal dieselbe ID (3) verwendet wurde, ist sicherlich ein Versehen, da die Daten nicht "echt" sind.

      Ja, da hast du recht.

      Gruß, Lukas

  2. hi,

    Rechnungen sind so eingetragen:
    1,RG0001,120,0,RG
    2,RG0002,80,0,RG
    3,RG0003,100,0,RG
    usw.

    Gutschriften stehen so drin:
    3,GU0001,50,2,GU
    4,GU0002,10,2,GU

    Soll und Haben mit dem gleichen Vorzeichen? Das würde ich ändern...

    Meine Frage: Ich würde gerne alle Rechnungssummen aufführen, das alleine ist kein Problem. Aber mir macht es ein Problem, die Rechnungsrestsummen aufzuführen, jedenfalls wenn ich nur 1 Query benutzen will. Hierbei macht mir der Abzug der Summe der Gutschriften das Problem.

    ... dann klappts auch mit dieser Abfrage.

    MfG

    1. Soll und Haben mit dem gleichen Vorzeichen? Das würde ich ändern...

      Hat erstens nichts mit Soll und Haben zu tun und zweitens sind Gutschriften und Rechnungen immer mit demselben Vorzeichen. Anders siehts bei Stornos aus, die werden anders gebucht.

      ... dann klappts auch mit dieser Abfrage.

      Ist ebenfalls Unfug. Die Abfrage hat mit dem Vorzeichen gar nichts zu tun.

      Lukas

      1. hi,

        Hat erstens nichts mit Soll und Haben zu tun und zweitens sind Gutschriften und Rechnungen immer mit demselben Vorzeichen.

        Oops, vieleicht sollte ich mal mit meiner Bank reden, ob die solch ein Win-Win-Konto für mich einrichten könnten :)

        Anders siehts bei Stornos aus, die werden anders gebucht.

        Ach was, auch hier bestimmt das Vorzeichen: Storno Soll ergibt einen positiven, Storno Haben einen negativen Betrag, da wird einfach nur das Vorzeichen umgedreht.

        ... dann klappts auch mit dieser Abfrage.

        Ist ebenfalls Unfug. Die Abfrage hat mit dem Vorzeichen gar nichts zu tun.

        Deine Bastel-Abfrage vielleicht nicht, aber ich würde die Beträge einfach nur aufsummieren um den Saldo zu ermitteln, ein zweckmäßiges DB-Design vorausgesetzt und eine Kontoführung die ich nicht neu erfinden müsste.

        Schöne Grüße.

        1. Hi Rolf,

          Oops, vieleicht sollte ich mal mit meiner Bank reden...

          Hast Du schonmal eine Faktura programmiert, Rolf?

          Ich habe das vor ca. 7 Jahren gemacht und zuvor mit mehreren Steuerberatern darüber gesprochen, wie es am sinnvollsten und schlüssigsten ist (auch in Hinsicht auf Datevexport).

          Und anschließend habe ich entschieden, wie diesbzgl. die Vorzeichen zu setzen sind. Das ganze System läuft seit dieser Zeit in allen Modulen fehlerfrei. Vielleicht magst Du unter dieser Voraussetzung einsehen, daß ich nicht auf "Forenzuruf" irgendwelche Vorzeichen ändern werde.

          Das Problem, das sich gerade hier stellt, ist ja auch kein Vorzeichenproblem ansich, oder siehst Du das etwa so?

          Lukas

          1. Hi Lukas,

            Oops, vieleicht sollte ich mal mit meiner Bank reden...

            Hast Du schonmal eine Faktura programmiert, Rolf?

            Nicht komplett. Umso schlimmer und vor allem zeitfressender waren Änderungen an Legacy-Code die ich oft genug machen musste. Wobei: "Legacy-Code" ist da eher ein Begriff aus der Schmeichel- und Streichel-Ecke, Sichwort Onion-Pattern, Kraut- und Rübenwachstum ;)

            Ich habe das vor ca. 7 Jahren gemacht und zuvor mit mehreren Steuerberatern darüber gesprochen, wie es am sinnvollsten und schlüssigsten ist (auch in Hinsicht auf Datevexport).

            Mit dieser Vorgehensweise kommen wir zusammen, absolut!

            Und anschließend habe ich entschieden, wie diesbzgl. die Vorzeichen zu setzen sind. Das ganze System läuft seit dieser Zeit in allen Modulen fehlerfrei.

            Wehe, es muss mal was geändert werden :)

            Vielleicht magst Du unter dieser Voraussetzung einsehen, daß ich nicht auf "Forenzuruf" irgendwelche Vorzeichen ändern werde.

            Klar, sehe ich ein.

            Das Problem, das sich gerade hier stellt, ist ja auch kein Vorzeichenproblem ansich, oder siehst Du das etwa so?

            Den Hinweis Self-Join hast Du ja schon bekommen. Die Lösung wird darauf hinauslaufen, eine Datenmenge zu bekommen, die per Script bearbeitet bzw. berechnet werden kann. Sei's drum aus G ein + und aus R ein - zu machen, wenn alle Konten abgeglichen sind, darf es keine Differenz geben.

            Viele Grüße und viel Erfolg!

            1. Hi Lukas,

              Wehe, es muss mal was geändert werden :)

              Nein, das ist nichtmal so dramatisch, solange es nicht an die Grundsubstanz des Konzeptes geht.

              Den Hinweis Self-Join hast Du ja schon bekommen. Die Lösung wird darauf hinauslaufen, eine Datenmenge zu bekommen, die per Script bearbeitet bzw. berechnet werden kann.

              Mir gefällt dedlfix Correlated Subquery Idee ganz gut. Ich muß mal sehen, wie ich die in die Gesamtquery einbinden kann. Isoliert arbeitet sie schonmal recht gut.

              Sei's drum aus G ein + und aus R ein - zu machen, wenn alle Konten abgeglichen sind, darf es keine Differenz geben.

              So ist es. Vorzeichenwechsel findet hier und da zum rechnen natürlich statt. Den Rest rolle ich "von hinten" auf, indem ich den Handshake zwischen Faktura und Buchführung als gegeben hinnehme und von dort aus sozusagen "rückwärts" konzipiert habe.

              Viele Grüße und viel Erfolg!

              Danke, Lukas

              1. hi,

                So ist es. Vorzeichenwechsel findet hier und da zum rechnen natürlich statt. Den Rest rolle ich "von hinten" auf, indem ich den Handshake zwischen Faktura und Buchführung als gegeben hinnehme und von dort aus sozusagen "rückwärts" konzipiert habe.

                In solchen Sachen vermisse ich meinen Vater sehr, er war Buchhalter und Finanzwirtschaftler mit Leib und Seele und er hat mir Einiges beigebracht.

                Viele Grüße.

  3. Tach!

    Ich würde gerne alle Rechnungssummen aufführen, das alleine ist kein Problem. Aber mir macht es ein Problem, die Rechnungsrestsummen aufzuführen, jedenfalls wenn ich nur 1 Query benutzen will. Hierbei macht mir der Abzug der Summe der Gutschriften das Problem.

    Einfaches Summieren scheidet aus, weil du Rechnungsbeträge und Abzüge in Form von Gutschriften dasselbe Vorzeichen haben. Mag sein, dass in der Anzeige die Beträge genauso stehen müssen, aber das könnte man ja dabei umrechnen.

    Nun ja, wenn das trotzdem so bleiben soll, brauchst du also die zur Rechnung gehörenden anderen Beträge. Die kannst du zum Beispiel in einer Correlated Subquery ermitteln. Mit Joins würde ich hier nicht hantieren wollen. Ungefähr so:

    SELECT rechnungsdaten, rechnungsbetrag - (SELECT SUM(betrag) FROM selbe_tabelle WHERE id_passt_zur_rechnung_der_hauptquery) AS rest FROM tabelle ... GROUP BY rechnung

    dedlfix.

    1. Tach!

      Hi dedlfix,

      Einfaches Summieren scheidet aus, weil du Rechnungsbeträge und Abzüge in Form von Gutschriften dasselbe Vorzeichen haben.

      Scheidet doch ohnehin aus, weil die RG und GU oder ST keine gemeinsame (Haupt)ID haben, sondern "nur" über eine Relation zueinander geführt werden können.

      Nun ja, wenn das trotzdem so bleiben soll, brauchst du also die zur Rechnung gehörenden anderen Beträge. Die kannst du zum Beispiel in einer Correlated Subquery ermitteln. Mit Joins würde ich hier nicht hantieren wollen. Ungefähr so:

      SELECT rechnungsdaten, rechnungsbetrag - (SELECT SUM(betrag) FROM selbe_tabelle WHERE id_passt_zur_rechnung_der_hauptquery) AS rest FROM tabelle ... GROUP BY rechnung

      Das muß ich mir genauer ansehen. Ich melde mich dann hier wieder.

      Danke erstmal, Lukas

      1. Das muß ich mir genauer ansehen. Ich melde mich dann hier wieder.

        Danke erstmal, Lukas

        Hi dedlfix,

        ich habe mir das angesehen und es funktioniert auch soweit erstmal ganz gut. Zum eventuellen "nachvollziehen":

        Es gelingt mir noch nicht ganz, dieses Konstrukt in meine Gesamtquery einzubinden. Ich weiß aber auch noch nicht, woran es genau hakt. Deshalb muß ich noch etwas "Testarbeit" verrichten, bevor ich die nächste Frage stellen oder das Problem als gelöst verabschieden darf...

          
        --  
        -- Tabellenstruktur für Tabelle '_testtabelle'  
        --  
          
        CREATE TABLE IF NOT EXISTS _testtabelle (  
          ID int(6) NOT NULL AUTO_INCREMENT,  
          Nummer varchar(20) COLLATE latin1_general_cs NOT NULL DEFAULT '0',  
          Art char(2) COLLATE latin1_general_cs DEFAULT 'RG',  
          ReferenzID int(6) NOT NULL DEFAULT '0',  
          Betrag decimal(8,2) NOT NULL DEFAULT '0.00',  
          Datum date NOT NULL,  
          PRIMARY KEY (ID)  
        ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;  
          
        --  
        -- Daten für Tabelle '_testtabelle'  
        --  
          
        INSERT INTO _testtabelle (ID, Nummer, Art, ReferenzID, Betrag, Datum) VALUES  
        (1, '0001', 'RG', 0, 120.00, '2014-08-18'),  
        (2, '0002', 'RG', 0, 160.00, '2014-08-18'),  
        (3, '0003', 'RG', 0, 400.00, '2014-08-10'),  
        (5, '0001', 'GU', 3, 50.00, '2014-08-18'),  
        (6, '0002', 'GU', 3, 60.00, '2014-08-18');  
          
          
          
        SELECT Nummer, Art, Datum, Betrag AS Ursprungsbetrag, IFNULL( Betrag - (  
        SELECT SUM( Betrag )  
        FROM _testtabelle t1  
        WHERE t1.ReferenzID = t2.ID ) , Betrag ) AS rest  
        FROM _testtabelle t2  
        GROUP BY ID  
        
        

        Lukas

        1. Tach!

          SELECT Nummer, Art, Datum, Betrag AS Ursprungsbetrag, IFNULL( Betrag - (
          SELECT SUM( Betrag )
          FROM _testtabelle t1
          WHERE t1.ReferenzID = t2.ID ) , Betrag ) AS rest
          FROM _testtabelle t2
          GROUP BY ID

            
          Ob du das GROUP BY brauchst oder nicht, musst du dich nochmal fragen. Wenn es zu jeder Rechnung nur eine Zeile gibt, dann ist das nicht notwendig. Weiterhin möchtest du ja, wenn ich das richtig sehe, nur die Rechnungen als Ergebnis haben und dazu dann Informationen von zugehörigen Datensätzen. Die Hauptquery muss erstmal ohne die Subquery die gewünschten Ergebniszeilen bringen. Dazu müsstest du in der Haupt-Query die Ergebnismenge auf die Rechungsdatensätze einschränken, also WHERE Art='RG'. Und möglicherweise musst du in der Subquery ebenfalls die Art einschränken, wenn sich die Beträge nicht alle mit einfachem SUM() zum vom Rechnungsbetrag abziehbaren (oder hinzufügbaren, je nachdem) Betrag zusammenfassen lassen.  
            
            
          dedlfix.
          
          1. Hi dedlfix,

            Ob du das GROUP BY brauchst oder nicht, musst du dich nochmal fragen.
            Wenn es zu jeder Rechnung nur eine Zeile gibt, dann ist das nicht notwendig.

            Hatte ich schon gemerkt. Das 'GROUP BY' bewirkt in beiden Fällen nichts. Ich habe 2 Gutschriften zu RG mit der ID 3. Mit oder ohne 'GROUP BY' erreiche ich dasselbe Ergebnis.

            Die Beschränkung auf Art='RG' in der Hauptquery ist korrekt. In der Subquery brauche ich nicht mehr zu differenzieren, weil das in (hier nicht erwähnten weiteren Feldern) per Flag bereits zu diesem Zeitpunkt aussortiert ist.

              
            SELECT Nummer, Art, Datum, Betrag AS Ursprungsbetrag, IFNULL( Betrag - (  
            SELECT SUM( Betrag )  
            FROM _testtabelle t1  
            WHERE t1.ReferenzID = t2.ID ) , Betrag ) AS rest  
            FROM _testtabelle t2 WHERE t2.Art = 'RG'  
            
            

            Mein Problem ist die Stelle, an der ich das ganze einsetzen möchte. (ich hoffe, ich kürze jetzt sinnvoll und korrekt)

            SELECT  
            r.RID,  
            r.spalte1,  
            r.Spalte2,  
            m.Spalte1,  
            ...,  
              
            SUM(IFNULL(z.zahlung, 0.0)) AS Gezahlt,  
            r.betrag *(1-  
            (CASE  
            WHEN z.Skonto = 0 THEN 0  
            WHEN ISNULL(z.Skonto) THEN 0  
            ELSE  
            z.Skonto  
            END)  
            /100) - SUM(IFNULL(z.zahlung, 0.0)) AS Offen,  
            r.Nummer,  
            z.text,  
              
            ...  
              
            FROM rechnungstabelle r  
            LEFT JOIN zahlungen z ON r.RID = z.RID  
            LEFT JOIN mahnungen m ON (r.RID = m.RID AND m.aktiv = 1)  
            WHERE  
            ...  
            AND r.bezahlt != 1  
            AND rz.Spalte1 != '1'  
            AND r.Art='RG'  
            AND ...  
            GROUP BY  
            r.KundeID,  
            r.nummer  
            ORDER BY ...
            

            Ich möchte die abzuziehenden Gutschriften in das Alias 'Offen' integrieren, also an diese Stelle:

            • SUM(IFNULL(z.zahlung, 0.0)) ********** AS Offen

            ********** = - (SELECT SUM(...usw.

            Da knabbere ich grad dran.

            Lukas

            1. Tach!

              Wenn es zu jeder Rechnung nur eine Zeile gibt, dann ist das [GROUP BY] nicht notwendig.
              Hatte ich schon gemerkt. Das 'GROUP BY' bewirkt in beiden Fällen nichts. Ich habe 2 Gutschriften zu RG mit der ID 3. Mit oder ohne 'GROUP BY' erreiche ich dasselbe Ergebnis.

              Es wäre nur dann notwendig, wenn das einzelne Rechnungsposten wären. Ist nicht der Fall, also weg mit dem GROUP BY und Schwamm drüber über den Punkt.

              Ich möchte die abzuziehenden Gutschriften in das Alias 'Offen' integrieren, also an diese Stelle:

              • SUM(IFNULL(z.zahlung, 0.0)) ********** AS Offen
                ********** = - (SELECT SUM(...usw.
                Da knabbere ich grad dran.

              Hast du da ein konkretes Problem? Ich seh da jetzt erstmal nicht, was ich antworten könnte. Außer vielleicht, dass das CASE-Konstrukt durch ein einfaches IFNULL() ausgetauscht werden könnte, wenn das so wie es da steht alles ist und nicht noch weitere WHEN-Fälle fehlen.

              dedlfix.

              1. Moin,

                Hast du da ein konkretes Problem? Ich seh da jetzt erstmal nicht, was ich antworten könnte. Außer vielleicht, dass das CASE-Konstrukt durch ein einfaches IFNULL() ausgetauscht werden könnte, wenn das so wie es da steht alles ist und nicht noch weitere WHEN-Fälle fehlen.

                Ja, ich habe einen konkreten mysql-Fehler.

                Ich habe jetzt mal versucht, nur die Gutschriften als Summe zusammenzufassen. Ich habe das Konstrukt also an anderer Stelle eingebaut. Und erhalte einen Error1064 (use near FROM rechnungstabelle r2 ...)

                SELECT  
                r.RID,  
                r.spalte1,  
                r.Spalte2,  
                m.Spalte1,  
                ...,  
                  
                SUM(IFNULL(z.zahlung, 0.0)) AS Gezahlt,  
                r.betrag *(1-  
                (CASE  
                WHEN z.Skonto = 0 THEN 0  
                WHEN ISNULL(z.Skonto) THEN 0  
                ELSE  
                z.Skonto  
                END)  
                /100) - SUM(IFNULL(z.zahlung, 0.0)) AS Offen,  
                r.Nummer,  
                z.text,  
                  
                ...  
                #---- Neu ---------#  
                (SELECT SUM(r.betrag  
                FROM rechnungstabelle r2  
                WHERE r2.ReferenzID = r.RID AND r2.Art='GU') AS rest)  
                #---- Neu ---------#  
                  
                FROM rechnungstabelle r  
                LEFT JOIN zahlungen z ON r.RID = z.RID  
                LEFT JOIN mahnungen m ON (r.RID = m.RID AND m.aktiv = 1)  
                WHERE  
                ...  
                AND r.bezahlt != 1  
                AND rz.Spalte1 != '1'  
                AND r.Art='RG'  
                AND ...  
                GROUP BY  
                r.KundeID,  
                r.nummer  
                ORDER BY ...
                

                Wo mache ich den Fehler bei der Umsetzung?

                Lukas

                1. Tach!

                  Und erhalte einen Error1064 (use near FROM rechnungstabelle r2 ...)
                  (SELECT SUM(r.betrag
                  FROM rechnungstabelle r2
                  Wo mache ich den Fehler bei der Umsetzung?

                  Da fehlt eine simple Klammer-zu bei SUM().

                  dedlfix.

                  1. Hi,

                    Da fehlt eine simple Klammer-zu bei SUM().

                    Oops. Aber nicht nur. Eine weitere Klammer war falsch gesetzt (die vor dem Alias)

                      
                    ...  
                    #---- Neu ---------#  
                    (SELECT SUM(r.betrag)  
                    FROM rechnungstabelle r2  
                    WHERE r2.ReferenzID = r.RID AND r2.Art='GU') AS rest  
                    #---- Neu ---------#  
                      
                    
                    

                    So funktioniert es.

                    Danke, dedlfix!

                    Lukas