Suche Hilfe, Select über mehrer Tabellen mit Join und SUM * und +

S

Steini

Benutzer
Beiträge
7
Hallo zusammen,

ich habe ein mittel- bis großes Problem und hoffe hier die nötige Hilfe zu bekommen.

Also, ich versuche es mal zu beschreiben:

Tabelle [Felder]
vorgang [id, rech_cat, rech_date, rech_nr, kunden_id, zahlungs_eingang]
positionen [id, rech_nr, artikel_id, menge, pos_zusatz_id, pos_auftrag, sort, artikel_frei_id]
kunden [id, kunden_id, firma_name, anrede, vorname, nachname, plz, ort, strasse....]
artikel [id, text, beschreibung, einheit, preis]
artikel_frei [id, rech_nr, artikel_frei_id, text, beschreibung, einheit, preis]

Ich versuch jetzt folgende Ausgabe zu bekommen:
Datum, RechnungsNr, KundenName, FimenName, Summe, Buchungsdatum

wobei das Problem meiner Meinung nach bei der Summe besteht.
In Tabelle "vorgang" und "positionen" steht die Rechnungs-Nr.
In der Tabelle "positionen" stehen die einzelnen Positionen zur jeweiligen Rechnung
mit Mengenangaben und die dazugehörige Artikel_ID oder Artikel_frei_ID aus den Tabellen
"Artikel" und "Artikel_frei".

Jede einzelne Mengenangabe aus Tabelle "positionen" muß mit dem Preis aus der Tabelle
"artikel" und/oder "artikel_frei" multipliziert werden so das eine Gesamtsumme pro Vorgang
ausgegeben wird, wäre etwa so etwas
SUM(P.menge * A.preis) + IFNULL(SUM(P.menge * F.preis), 0)

Ich habe irgendwie schon alle Variationen von JOIN , UNION und was weiß ich noch alles durch.
Ich komme einfach nicht weiter.

bei folgendem Beispiel bekomme ich aber leider nur einen Vorgang ausgegeben:

"SELECT V.*, K.nachname, K.vorname, K.firma_name,
SUM(P.menge * A.preis) + IFNULL(SUM(P.menge * F.preis), 0) AS PREIS
FROM vorgang AS V, kunden AS K, positionen as P
LEFT JOIN artikel AS A ON P.artikel_id = A.id
LEFT JOIN artikel_frei AS F ON P.artikel_frei_id = F.artikel_frei_id
WHERE V.kunden_id = K.kunden_id
AND V.rech_nr = P.rech_nr
AND V.rech_cat = 'RE'"


Wäre prima wenn mir da jemand auf die Sprünge helfen könnte.

Achso, falls es wichtig ist, ich möchte das per PHP Ausgabe.

Vielen Dank schonmal, hoffe ist einigermaßen verständlich
 
Werbung:
Steini schrieb:
Hallo zusammen,

ich habe ein mittel- bis großes Problem und hoffe hier die nötige Hilfe zu bekommen.

Also, ich versuche es mal zu beschreiben:
Zum Vergrößern anklicken....


Mal mittel- bis stark vereinfacht:

Code: 
test=# select * from vorgang;                 
 id                                           
----                                          
  1                                           
  2                                           
(2 rows)                                      

Time: 0,175 ms
test=*# select * from artikel;
 id | preis                   
----+-------                  
  1 |    10                   
  2 |    20                   
  3 |    30                   
(3 rows)                      

Time: 0,178 ms
test=*# select * from artikel_frei;
 id | preis                        
----+-------                       
  1 |    15                        
  2 |    25                        
  3 |    35                        
(3 rows)                           

Time: 0,186 ms
test=*# select * from position;
 id | rechnung | artikel | artikel_frei
----+----------+---------+--------------
  1 |        1 |       1 |
  2 |        1 |         |            2
  3 |        2 |       2 |            3
(3 rows)

das als Ausgang.

Als Zwischenschritt:
Code: 
test=*# select p.rechnung, a1.preis, a2.preis from position p left join artikel a1 on p.artikel=a1.id left join artikel_frei a2 on p.artikel_frei=a2.id;
 rechnung | preis | preis
----------+-------+-------
        1 |    10 |
        1 |       |    25
        2 |    20 |    35
(3 rows)

Final:
Code: 
test=*# select rechnung, sum(coalesce(preis,0))+sum(coalesce(preis_frei)) from (select p.rechnung, a1.preis, a2.preis as preis_frei from position p left join artikel a1 on p.artikel=a1.id left join artikel_frei a2 on p.artikel_frei=a2.id) foo group by 1;
 rechnung | ?column?
----------+----------
        1 |       35
        2 |       55
(2 rows)

Den Rest per JOIN dranzumontieren schaffst Du ;-)

Du hast in Deinem Versuch schon mal Fehler, auch wenn MySQL das wohl verzeiht: Alle Spalten des Resultats, welche nicht Bestandteil einer Aggregation sind, gehören in das GROUP BY. Das ist SQL-Norm und ALLE Datenbanken erzwingen das, außer MySQL. MySQL errät hier irgendwas, manchmal das, was der Benutzer will. Aber nicht immer.
 
ich werd verrückt, und ich brech mir hier einen ab.....
VIELEN VIELEN DANK @ akretschmer

ich hab es jetzt genau so wie ich es haben wollte:
Code: 
select V.*, K.nachname,K.firma_name, P.rech_nr, SUM(coalesce(A1.preis,0) * P.menge) + SUM(coalesce(A2.preis,0) * P.menge) AS PREIS
FROM positionen P
LEFT JOIN vorgang V ON P.rech_nr=V.rech_nr
LEFT JOIN kunden K ON V.kunden_id=K.kunden_id
LEFT JOIN artikel A1 ON P.artikel_id=A1.id
LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
GROUP BY P.rech_nr

bei der Gelegenheit habe ich mir auch noch einmal die "coalesce" Funktion genauer angeschaut (auch sehr nützlich)

(ich hoffe in der Abfrage sind keine weiteren Fehler und ich die gewünschte Ausgabe nur zufällig erhalte)

LG Steini
 
Steini schrieb:
ich werd verrückt, und ich brech mir hier einen ab.....
VIELEN VIELEN DANK @ akretschmer

ich hab es jetzt genau so wie ich es haben wollte:
Code: 
select V.*, K.nachname,K.firma_name, P.rech_nr, SUM(coalesce(A1.preis,0) * P.menge) + SUM(coalesce(A2.preis,0) * P.menge) AS PREIS
FROM positionen P
LEFT JOIN vorgang V ON P.rech_nr=V.rech_nr
LEFT JOIN kunden K ON V.kunden_id=K.kunden_id
LEFT JOIN artikel A1 ON P.artikel_id=A1.id
LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
GROUP BY P.rech_nr

bei der Gelegenheit habe ich mir auch noch einmal die "coalesce" Funktion genauer angeschaut (auch sehr nützlich)

(ich hoffe in der Abfrage sind keine weiteren Fehler und ich die gewünschte Ausgabe nur zufällig erhalte)

LG Steini
Zum Vergrößern anklicken....

Doch, sind Fehler.
  • kein select * in produktivem Code verwenden
  • alle Spalten, die nicht in einer Aggregatfunktion sind, gehören in das GROUP BY

Änderst Du später mal Deine Tabelle Vorgang (v) stimmt Dein Select hier nicht mehr, in verbindung mit dem, ich muß schon sagen Bug, daß MySQL es nicht erzwingt, daß alle Spalten im GROUP BY sein müssen, wird das Resultat Deiner Abfrage (auch jetzt schon) eher zufällig sein.

Andreas
 
OK, hab ich jetzt angepasst

Code: 
select
V.id, V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id,
SUM(coalesce(A1.preis,0) * P.menge) + SUM(coalesce(A2.preis,0) * P.menge) AS PREIS
FROM positionen P
LEFT JOIN vorgang V ON P.rech_nr=V.rech_nr
LEFT JOIN kunden K ON V.kunden_id=K.kunden_id
LEFT JOIN artikel A1 ON P.artikel_id=A1.id
LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
WHERE rech_cat = 'RE'
GROUP BY V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id

wenn obiger Code keine Fehler mehr enthält habe ich aber jetzt doch noch ein Problem mit dem "ORDER BY"
vieleicht sehe ich ja auch den Wald vor lauter Bäume nicht mehr.
 
Steini schrieb:
OK, hab ich jetzt angepasst

Code: 
select
V.id, V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id,
SUM(coalesce(A1.preis,0) * P.menge) + SUM(coalesce(A2.preis,0) * P.menge) AS PREIS
FROM positionen P
LEFT JOIN vorgang V ON P.rech_nr=V.rech_nr
LEFT JOIN kunden K ON V.kunden_id=K.kunden_id
LEFT JOIN artikel A1 ON P.artikel_id=A1.id
LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
WHERE rech_cat = 'RE'
GROUP BY V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id

wenn obiger Code keine Fehler mehr enthält habe ich aber jetzt doch noch ein Problem mit dem "ORDER BY"
vieleicht sehe ich ja auch den Wald vor lauter Bäume nicht mehr.
Zum Vergrößern anklicken....

Mit dem ORDER BY sehe ich kein Problem, da ich kein ORDER BY sehe.

Andreas
 
mh, zu früh gefreut...

eine Rechnung "V.rech_nr" ist in Tabelle "VORGANG" angelegt aber es wurde noch keine Position angelegt, demnach ist in der Tabelle "POSITIONEN" noch kein Datensatz "P.rech_nr" vorhanden.
Der Vorgang fehlt dann natürlich bei der Ausgabe. Sonst sieht die Ausgabe schon perfekt aus.
Code: 
select
V.id, V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id,
SUM(coalesce(A1.preis,0) * coalesce(P.menge,0)) + SUM(coalesce(A2.preis,0) * coalesce(P.menge,0)) AS PREIS
FROM positionen P
LEFT JOIN vorgang V ON P.rech_nr = V.rech_nr
LEFT JOIN kunden K ON V.kunden_id = K.kunden_id
LEFT JOIN artikel A1 ON P.artikel_id = A1.id
LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
GROUP BY V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet, K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id

LEFT JOIN vorgang V ON P.rech_nr = V.rech_nr


Macht das jetzt Sinn einen leeren Datensatz in der Tabelle "POSITIONEN" anzulegen oder
kann man das in der Abrage noch irgendwie abfangen ?
 
oh man, das Leben könnte soooo einfach sein wenn man öfter mal die Augen auf macht....

hier jetzt meine fertige Abfrage, scheint so das alles jetzt funktioniert:

Code: 
select
V.id, V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id,
SUM(coalesce(A1.preis,0) * coalesce(P.menge,0)) + SUM(coalesce(A2.preis,0) * coalesce(P.menge,0)) AS PREIS
FROM vorgang V
LEFT JOIN positionen P ON P.rech_nr = V.rech_nr
LEFT JOIN kunden K ON V.kunden_id = K.kunden_id
LEFT JOIN artikel A1 ON P.artikel_id = A1.id
LEFT JOIN artikel_frei A2 ON P.artikel_frei_id = A2.artikel_frei_id
GROUP BY V.rech_nr, V.rech_cat, V.rech_date, V.zahlungs_eingang, V.versendet, K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id

ich habe da zwar noch dynamische WHERE und ORDER BY Klauseln drin die per PHP generiert werden aber das ist ja nur Spielerei am Rande.

Funktionirt SUPER, VIELEN DANK für die schnelle und vor allem professionelle Hilfe..
 
Werbung:

Ähnliche Themen

R
Select über 2 Tabellen mit Summen - Zusammenfassung
Antworten
3
Aufrufe
2K
akretschmer
akretschmer
R
SQL Abfrage letzter Preis für Artikel
Antworten
4
Aufrufe
4K
rolandg1881
R
A
Multiple Joins über mehrere Tabellen
Antworten
8
Aufrufe
4K
norbert_pdm
norbert_pdm
D
JOIN mit 3 Tabellen
Antworten
3
Aufrufe
2K
akretschmer
akretschmer
P
JOIN über 3 Tabellen mit SUM-Funtion
Antworten
2
Aufrufe
3K
peter_w
P
Zurück
Oben