phpMyAdmin: 2 Abfragen verknüpfen (eine Ergebnisseite)

[Jean-Max]

Bounjoun alle,

ich knüpfe an meine letzte (welche auch die erste war) Datenbankfrage an, und frage nun, wie ich die zwei folgenden Abfragen in einer einzigen Abfrage kombinieren kann.

Zum Verständnis: In meiner Datenbank »Gewerbe« gibt es eine Tabelle »kunden« mit den Kundendaten (inkl. Kundennummer) und eine Tabelle »kunden_rechnungen« mit den Rechnungsdetails und derselben Kundennummer für den jeweiligen Datensatz.

Dank der Tipps aus dem verlinkten Posting und eigener Recherche kann ich mir alle offenen Rechnungen anzeigen lassen:

SELECT `kunden`.`Kundennummer` , `kunden`.`Firma` , `kunden_rechnungen`.`Rechnungsnummer` , `kunden_rechnungen`.`Rechnungsdatum` , `kunden_rechnungen`.`Rechnungsbetrag` , `kunden_rechnungen`.`Gewerbesparte` , `kunden_rechnungen`.`Status`  
FROM `kunden`  
LEFT JOIN `kunden_rechnungen` ON `kunden`.`Kundennummer` = `kunden_rechnungen`.`Kundennummer`  
WHERE (  
(  
`kunden_rechnungen`.`Status` = 'offen'  
)  
)

Ergebnis:

Kundennummer|Firma|Rechnungsnummer|Rechnungsdatum|Rechnungsbetrag|Gewerbesparte|Status
    1-TR    | AAA |       42      |  2011-07-03  |    1880.56    |  TRANSPORT  | offen
    3-WD    | AAB |       49      |  2011-07-29  |     797.50    |  WEBDESIGN  | offen
    2-TR    | BAA |       52      |  2011-08-05  |    3215.17    |  TRANSPORT  | offen

als auch die Summe der offenen Rechnungen:

SELECT sum(Rechnungsbetrag) AS "SUMME" FROM `kunden_rechnungen`  
WHERE  (`Status` = 'offen')

Ergebnis:

SUMME
 5893.23

Was ich aber gerne als Ergebnis hätte, wäre sowas wie:

Kundennummer|Firma|Rechnungsnummer|Rechnungsdatum|Rechnungsbetrag|Gewerbesparte|Status
    1-TR    | AAA |       42      |  2011-07-03  |    1880.56    |  TRANSPORT  | offen
    3-WD    | AAB |       49      |  2011-07-29  |     797.50    |  WEBDESIGN  | offen
    2-TR    | BAA |       52      |  2011-08-05  |    3215.17    |  TRANSPORT  | offen
                                                      5893.23

wobei es keine Rolle spielt, ob die Summe nun unten den Rechnungsbeträgen aufgelistet wird oder sonstwo am Anfang einer Zeile darunter, ist ja keine Design-Aufgabe ;)

Verständliche Lese-Tipps sind willkommen, nach ca. 1 Stunde Googlen stelle ich fest, dass die Frage oft gestellt wird, die Antworten allerdings oft daneben sind, weil die OPs die Fragen nicht richtig formuliert hatten...? Das Stichwort »Unterabfrage« mit einer Syntax wie: SELECT (SELECT) ist eins, zweimal gefallen, doch da komme ich auch nicht weiter.

Adiou.

[ChrisB]

Hi,

und frage nun, wie ich die zwei folgenden Abfragen in einer einzigen Abfrage kombinieren kann.

UNION.

MfG ChrisB

[Jean-Max]

Bounjoun ChrisB,

Danke schonmal,

UNION.

hört sich vielsprechend an, doch außer Syntax-Errors kommt nichts bei raus. »Dann mache ich etwas falsch«, würde Struppi sagen. Nur was?

(SELECT `kunden`.`Kundennummer` , `kunden`.`Firma` , `kunden_rechnungen`.`Rechnungsnummer` , `kunden_rechnungen`.`Rechnungsdatum` , `kunden_rechnungen`.`Rechnungsbetrag` , `kunden_rechnungen`.`Gewerbesparte` , `kunden_rechnungen`.`Status`  
FROM `kunden`  
LEFT JOIN `kunden_rechnungen` ON `kunden`.`Kundennummer` = `kunden_rechnungen`.`Kundennummer` WHERE (`kunden_rechnungen`.`Status` = 'offen')  
)  
UNION  
(SELECT sum(`kunden_rechnungen`.`Rechnungsbetrag`)  
WHERE(`kunden_rechnungen`.`Status` = 'offen')  
)

(

nach der auf der eben verlinkten Dokuseite vorgestellte Syntax:

(SELECT a FROM t1 WHERE a=10 AND B=1)  
UNION  
(SELECT a FROM t2 WHERE a=11 AND B=2)  
ORDER BY a LIMIT 10;

)

Das Verhältnis Klammer-offen/Klammer-zu stimmt, also wo ist der Wurm versteckt? phpMyAdmin sagt, ich sollte den Manual checken:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE(kunden\_rechnungen.Status = 'offen') )' at line 6

was natürlich super hilfreich ist, zumal es immer dieselbe Meldung, egal, welcher Fehler vorhanden ist :( - da lobe ich mir die Perl-Meldungen, echt!

Adiou.

[Jean-Max]

Re!

Bin schon ein Stück weitergekommen... Mit dieser Abfrage:

(  
SELECT `kunden`.`Kundennummer` , `kunden`.`Firma` , `kunden_rechnungen`.`Rechnungsnummer` , `kunden_rechnungen`.`Rechnungsdatum` , `kunden_rechnungen`.`Rechnungsbetrag` , `kunden_rechnungen`.`Gewerbesparte` , `kunden_rechnungen`.`Status`  
FROM `kunden`  
LEFT JOIN `kunden_rechnungen` ON `kunden`.`Kundennummer` = `kunden_rechnungen`.`Kundennummer`  
WHERE (  
`kunden_rechnungen`.`Status` = 'offen')  
)  
UNION (  
  
SELECT "", "", "", "", sum( `kunden_rechnungen`.`Rechnungsbetrag` ) , "", ""  
FROM `kunden_rechnungen`  
WHERE (  
`kunden_rechnungen`.`Status` = 'offen'))

komme ich fast zum gewünschten Ergebnis...

Leider werden mir alle Fließkommazahlen angezeigt, anstatt nur 2:

Kundennummer|Firma|Rechnungsnummer|Rechnungsdatum|Rechnungsbetrag |Gewerbesparte|Status
    1-TR    | AAA |       42      |  2011-07-03  |1880.56005859375|  TRANSPORT  | offen

Noch' Tippchen?

Adiou.

[ChrisB]

Hi,

Leider werden mir alle Fließkommazahlen angezeigt, anstatt nur 2:

Du meinst Nachkommastellen?

Noch' Tippchen?

Works as desigend, siehe Beschreibung von UNION.

Runden sollte helfen.

MfG ChrisB

[MudGuard]

Hi,

Leider werden mir alle Fließkommazahlen angezeigt, anstatt nur 2:
Du meinst Nachkommastellen?
Works as desigend, siehe Beschreibung von UNION.

Runden sollte helfen.

Da es sich um Geldbeträge handelt, würde ich eher dazu raten, keine Fließkommazahlen für diese Spalten zu benutzen (sondern z.B. Ganzzahlen in Cent) ...

cu,
Andreas

[Jean-Max]

Bounjoun MudGuard und ChrisB,

Leider werden mir alle Fließkommazahlen angezeigt, anstatt nur 2:
Du meinst Nachkommastellen?
Works as desigend, siehe Beschreibung von UNION.
Runden sollte helfen.

Hm, eine Idee in den Raum werf...

In der Tabelle »kunden_rechnungen« ist das Feld »Rechnungsbetrag« vom Typ FLOAT(10,2). Kann das eine Rolle spielen? Beim Ergebnis der Anfrage wird die »längste« Zahl mit 13 (dreizehn) Nachkommastellen angezeigt.

Christoph, bei der Beschreibung von UNION finde ich nichts, worauf du im letzten Post anspielst. Oder meintest du REPEAT? Hier die Seite (die ich im gestrigen Post vergessen hatte, zu verlinken):

http://dev.mysql.com/doc/refman/5.1/de/union.html

Da es sich um Geldbeträge handelt, würde ich eher dazu raten, keine Fließkommazahlen für diese Spalten zu benutzen (sondern z.B. Ganzzahlen in Cent) ...

Ich würde ungerne die Rechnungen-Tabelle umgestalten.

Adiou, derweil weiter Literatur 'reinziehend...

[Jean-Max]

Bounjoun Jean-Max, MudGuard und ChrisB,

(  
SELECT `kunden`.`Kundennummer` , `kunden`.`Firma` , `kunden_rechnungen`.`Rechnungsnummer` , `kunden_rechnungen`.`Rechnungsdatum` , ROUND( `kunden_rechnungen`.`Rechnungsbetrag` , 2 ) , `kunden_rechnungen`.`Gewerbesparte` , `kunden_rechnungen`.`Status`  
FROM `kunden`  
LEFT JOIN `kunden_rechnungen` ON `kunden`.`Kundennummer` = `kunden_rechnungen`.`Kundennummer`  
WHERE (  
`kunden_rechnungen`.`Status` = 'offen')  
)  
UNION (  
  
SELECT "", "", "", "", ROUND( sum( `kunden_rechnungen`.`Rechnungsbetrag` ) , 2 ) , "", ""  
FROM `kunden_rechnungen`  
WHERE (  
`kunden_rechnungen`.`Status` = 'offen'))

Danke an allen Beteiligten!

Adiou, kann mich jetzt anderen Sachen widmen, z.B. Tee trinken ;)

[Jean-Max]

Re!

das Feld »Rechnungsbetrag« ist vom Typ FLOAT(10,2).

^^

Und es kommt keine lakonische Bemerkung, weil ich die Milliarde Umsatz anpeile?

:)

Adiou.

[Der Martin]

Hallo,

das Feld »Rechnungsbetrag« ist vom Typ FLOAT(10,2).
                                                ^^
Und es kommt keine lakonische Bemerkung, weil ich die Milliarde Umsatz anpeile?

nein, warum denn auch? Es ist doch völlig in Ordnung, sich selbst lohnende Ziele zu setzen. ;-)

Schönen Sonntag noch,
 Martin

[ChrisB]

Hi,

das Feld »Rechnungsbetrag« ist vom Typ FLOAT(10,2).

Und es kommt keine lakonische Bemerkung, weil ich die Milliarde Umsatz anpeile?

Nein, da lächeln wir nur still drüber - weil wir wissen, dass du die bei Verwendung falscher Datentypen sowieso nicht erreichen wirst :-p

MfG ChrisB

[ChrisB]

Hi,

In der Tabelle »kunden_rechnungen« ist das Feld »Rechnungsbetrag« vom Typ FLOAT(10,2). Kann das eine Rolle spielen?

Ja, FLOAT bildet keine exakten Werte ab.

Da es sich um Geldbeträge handelt, würde ich eher dazu raten, keine Fließkommazahlen für diese Spalten zu benutzen (sondern z.B. Ganzzahlen in Cent) ...

Ich würde ungerne die Rechnungen-Tabelle umgestalten.

Solltest du aber, weil FLOAT definitiv der falsche Datentyp für sowas ist.

Müssen nicht unbedingt Centbeträge als INT sein, MySQL kennt für genau solche Zwecke nämlich auch DECIMAL. Das besitzt eine anzugebende Genauigkeit an Nachkommastellen, und garantiert diese auch.

Christoph, bei der Beschreibung von UNION finde ich nichts, worauf du im letzten Post anspielst.

Ich bezog mich auf
“If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.”
in der englischen Fassung. (Hatte nicht angenommen, dass du die deutsche Übersetzung nutzt. Das ist doch, wenn man's „genau wissen“ will, meistens keine gute Idee, bei PHP analog.)

Wenn du das „Problem“, dass dir mehr als zwei Nachkommastellen geliefert werden, vorher mit der einfachen Query, die nur alle Datensätze liefert, nicht hattest, und es erst durch das Bilden einer SUMme auftritt - dann siehst du da schon den Effekt der immanenten Ungenauigkeit von FLOAT-Zahlen (denn das Bilden einer Summe an sich erzeugt selber nicht mehr Nachkommastellen, als vorher maximal vorhanden waren).

Also: Spaltentyp *ÄNDERN*

MfG ChrisB

[Jean-Max]

Bounjoun ChrisB,

Müssen nicht unbedingt Centbeträge als INT sein, MySQL kennt für genau solche Zwecke nämlich auch DECIMAL. Das besitzt eine anzugebende Genauigkeit an Nachkommastellen, und garantiert diese auch.

Jetzt kann ich mich erinnern, dass ich DECIMAL zuerst gewählt hatte, als ich die Datenbank angelegt hatte. Doch mit irgendwas war ich nicht klar gekommen, weswegen ich dann FLOAT probiert hatte.

Vermutlich hatte ich bei DECIMAL vergessen die Länge anzugeben ;)

Wenn ich das jetzt benutze, werden allerdings alle Beträge linksbündig angeordnet, was bei vielen Datensätzen mit unterschiedlich langen Zahlen etwas bescheuert aussieht...

“If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.”

Ich habe die Stelle in der deutschen Beschreibung auch gefunden, die englische ist allerdings verständlicher...

Wenn du das „Problem“, dass dir mehr als zwei Nachkommastellen geliefert werden, vorher mit der einfachen Query, die nur alle Datensätze liefert, nicht hattest, und es erst durch das Bilden einer SUMme auftritt

Nein, bei der reinen Summe gab es 2 Nachkommastellen. Erst beim Unterabfragen mit UNION kommen (bei FLOAT) die vielen Nachkommazahlen - und zwar in allen Rechnungsbeträgen, nicht nur unten bei der Summe.

Wenn ich DECIMAL benutze und mit UNION die Abfragen kombiniere, sind alle Felder mit reinen Zahlen linksbündig angeordnet, auch die reinen Integer wie Rechnunsnummer oder Kundennummer.

Adiou.

[ChrisB]

Hi,

Wenn ich das jetzt benutze, werden allerdings alle Beträge linksbündig angeordnet, was bei vielen Datensätzen mit unterschiedlich langen Zahlen etwas bescheuert aussieht...

Es ist nicht Aufgabe der Query, irgendwas schön lesbar zu formatieren, die hat nur die Daten zu liefern.

Die Ausgabe wirst du doch sowieso nachgelagert in einem Script machen, oder etwa nicht? Dann formatiere da wie gewünscht.

Wenn ich DECIMAL benutze und mit UNION die Abfragen kombiniere, sind alle Felder mit reinen Zahlen linksbündig angeordnet, auch die reinen Integer wie Rechnunsnummer oder Kundennummer.

Für INT kennt MySQL zwar das Attribut ZEROFILL, bzw. bei fehlen von diesem füllt es Zahlen automatisch bis zur angegebenen Länge mit Spaces auf, aber:

Note
The ZEROFILL attribute is ignored when a column is involved in expressions or UNION queries.

(http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html)

Du könntest natürlich das ganze als Subquery nutzen und noch mal in ein weiteres SELECT „verpacken“, in dem du dann mittels Stringfunktionen und ggf. CASTs das ganze noch optisch „aufbereitest“ - aber das ist m.E. nicht mehr wirklich der Sinn von SQL.

MfG ChrisB

[Jean-Max]

Bounjoun ChrisB,

Es ist nicht Aufgabe der Query, irgendwas schön lesbar zu formatieren, die hat nur die Daten zu liefern.
Die Ausgabe wirst du doch sowieso nachgelagert in einem Script machen, oder etwa nicht? Dann formatiere da wie gewünscht.

phpMyAdmin ist für das Webinterface zuständig. Ich wundere mich nur, warum die Anzeige unterschiedlich ist, je nach Art der Abfrage. Dass die Query nur die Daten bereitstellt, leuchtet auch mir ein ;)

Auch die Spaltensortierfunktion ist erstens anderes dargestellt nach einem Union: Button statt Text - und zweitens auch noch funktionslos.

Werde mir wohl in Perl was basteln müssen, für die Anzeige.

Danke für die weiteren Erläuterungen!

Adiou.

[Jean-Max]

Re!

Auch die Spaltensortierfunktion ist erstens anderes dargestellt nach einem Union: Button statt Text - und zweitens auch noch funktionslos.

Ah, das mit den Buttons lag nur an der Klammerung der Statements (s. Posting mit dem Code). Ohne Klammer ist zumindest das wie gewohnt, also Spaltennamen als Text und mit funktionierender Sortierfunktion. Wenn einer hier weiß, was phpMyAdmin da bastelt, her damit ;)

Werde mir wohl in Perl was basteln müssen, für die Anzeige.

Das bleibt aber dennoch auf der ToDo-Liste.

Adiou.