e51bomag
Goto Top

Hilfe bei SQL Abfrage

Habe einfach eine Denkblockade und würde mich über Hilfe bei einer Datenbankabfrage freuen.

Die Abfrage lautet wie folgt:
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '2') AND '2' = bills.kid AND '2' = kaeufe.kid;

Sinn soll es sein, die Gesamtsumme von Posten zu bekommen, welche seit der letzten Rechnung angefallen sind.
Tabellen:
bills (Rechnungen)
kaeufe (getätigte Käufe)
kunden (Kunden)
positionen (Artikel)

Verknüpfung:
bills.kid <-> kunden.kid <-> kaeufe.kid, kaeufe.pid <-> positionen.pid

Derzeit erhalte ich aber als Abfrage in einem Beispiel folgendes:
21€
da stehen sollten aber 10,50€

Tabelle kaeufe:
kaid kid pid datum
1 2 1 2015-05-12 13:10:46
2 2 2 2015-05-12 13:31:05
3 2 1 2015-05-12 14:56:34
4 2 3 2015-05-12 14:56:43

Tabelle bills:
bid kid gesamt datum status
1 2 0 2015-05-11 00:00:00 Account erstellt
4 2 1.5 2015-05-12 14:33:42 bezahlt
5 4 0 2015-05-12 14:56:17 Account erstellt

Tabelle positionen:
pid bezeichnung inhalt preis
1 Mixery 0,5l 0.5
2 Cola 1,5l 1
3 Kiste Bier 10l 10

Content-ID: 272214

Url: https://administrator.de/forum/hilfe-bei-sql-abfrage-272214.html

Ausgedruckt am: 23.12.2024 um 10:12 Uhr

ukulele-7
ukulele-7 18.05.2015 um 10:10:41 Uhr
Goto Top
Kannst du auch noch die betroffenden Datensätze aus "positionen" posten? Daraus ergibt sich ja deine Summe, ohne die kann ich nur raten. Alle Datensätze mit id 1 bis 3.
e51bomag
e51bomag 18.05.2015 aktualisiert um 11:33:27 Uhr
Goto Top
Habe diese noch eingefügt

//EDIT
Ein SELECT SUM(DISTINCT preis)... ist leider auch keine Lösung. Sobald 2 gleiche Posten "gekauft" wurden, werden diese als doppelte Datensätze gefiltert
ukulele-7
ukulele-7 18.05.2015 um 11:32:52 Uhr
Goto Top
Dein Problem liegt auf jedenfall in der bills Tabelle. Dort kann es scheinbar pro Rechnung mehrere Datensätze geben mit verschiedenen Status und Datum-/Zeitangaben. Das ist nicht zwingend falsch wenn auch vieleicht etwas unglücklich aufgebaut. Auf diese zwei unterschiedlichen Status joinst du dann in beiden Fällen alle Positionen, hast also alle doppelt.

Das kann man mit bills.[status] = 'bezahlt' lösen aber ob das sinnvoll ist kann man anhand der drei Datensätze nicht sagen.

Hier mein Testcode:
WITH kaeufe(kaid,kid,pid,datum) AS (
SELECT 1,2,1,'2015-05-12 13:10:46.000'
UNION ALL
SELECT 2,2,2,'2015-05-12 13:31:05.000'
UNION ALL
SELECT 3,2,1,'2015-05-12 14:56:34.000'
UNION ALL
SELECT 4,2,3,'2015-05-12 14:56:43.000'
), bills(bid,kid,gesamt,datum,[status]) AS (
SELECT 1,2,0,'2015-05-11 00:00:00','Account erstellt'
UNION ALL
SELECT 4,2,1.5,'2015-05-12 14:33:42','bezahlt'
UNION ALL
SELECT 5,4,0,'2015-05-12 14:56:17','Account erstellt'
), positionen(pid,bezeichnung,preis) AS (
SELECT 1,'Mixery 0,5l',0.5
UNION ALL
SELECT 2,'Cola 1,5l',1
UNION ALL
SELECT 3,'Kiste Bier 10l',10
)

SELECT sum(preis) AS gesamt
FROM positionen
INNER JOIN kaeufe
ON positionen.pid = kaeufe.pid
--INNER JOIN kunden
--ON kaeufe.kid = kunden.kid
INNER JOIN bills
ON kaeufe.kid = bills.kid
--AND bills.[status] = 'bezahlt' --die Bedingung löst das Problem zumindest für die Testdaten
--ON kunden.kid = bills.kid
WHERE kaeufe.datum > ( SELECT max(bills.datum)
FROM bills
WHERE bills.kid = '2')
AND '2' = bills.kid
--AND '2' = kaeufe.kid --unnötig, da bills.kid = kaeufe.kid
e51bomag
e51bomag 18.05.2015 um 11:43:13 Uhr
Goto Top
Das AND bills.status = 'bezahlt' soll sicherlich nach dem WHERE oder?

SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '2') AND '2' = bills.kid AND bills.status = 'bezahlt'

Das funktioniert leider auch nicht nicht wirklich. Im ersten Augenblick ja, aber nach dem weitere Rechnungen in bills dazu kommen, kommt da wieder etwas durch einander.

Vielleicht hilft folgender Prozess:
Nutzer legen Käufe an. Bei Bedarf können diese auf "Bezahlen" klicken. Es wird eine Rechnung angelegt mit Status "offen". Der Status "Account erstellt" wird bei Nutzerregistrierung eingetragen als Vergleichsdatum (hätte man sicherlich auch bei kunden mit einfügen können). Anschließend kann ein Administrator prüfen ob die Rechnung bezahlt wurde und diese dann auf Status "bezahlt" setzen.

Die jetzige Abfrage soll halt alle Käufe Summieren, welche seit der letzten bezahlten bzw. offenen Rechnung wieder angelaufen sind.
Biber
Lösung Biber 18.05.2015 aktualisiert um 11:56:22 Uhr
Goto Top
Moin e51bomag,

du hast zu viele JOINS.
Durch die Joins über die "kid" bekommst du in deinen Beispieldaten 4 Datenzeilen im Resultset statt 2.

Hinreichend wäre:
select sum(preis) as gesamt
FROM positionen
  INNER JOIN kaeufe
          ON positionen.pid = kaeufe.pid
--   INNER JOIN kunden
--           ON kaeufe.kid = kunden.kid
--   INNER JOIN bills
--           ON kunden.kid = bills.kid


WHERE kaeufe.datum > (SELECT max(bills.datum)
                      FROM bills
                      WHERE bills.kid = '2')  

-- AND   '2' = bills.kid  
AND   '2' = kaeufe.kid;  

Ich habe die überflüssigen Joins auskommentiert im Statement stehen lassen - falls dein SQL-Query-Tool das nicht ab kann, musst du alle Zeilen, die mit "-- " beginnen mal kurz entfernen.

Du kannst mal die erste Zeile ändern von "SELECT sum(bla) from Positionen" auf "SELECT * from positionen".

Dann wird reletiv deutlich, warum bei dem Join-Konstrukt mit 2xkid=2 in der kaeufe-Tabelle mal 2xkid=2 in der bills-Tabelle im Ergebnis 2x2=4 Datenzeilen zurückkommen.

Grüße
Biber
e51bomag
e51bomag 18.05.2015 aktualisiert um 11:57:20 Uhr
Goto Top
Werde ich auch noch testen, ansonsten hätte ich wohl jetzt eine Lösung durch den Hinweis zur "bills" Tabelle.

SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid where kaeufe.datum > bills.datum AND kunden.kid = bills.kid AND bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid')

Durch bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid') schaut er sich nur die entscheidende letzte Rechnung in bills an

//EDIT
Danke an euch beide, im Endeffekt funktioniert die letzte Lösung wohl am elegantesten.
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid') AND '$kid' = kaeufe.kid
ukulele-7
ukulele-7 18.05.2015 um 11:59:28 Uhr
Goto Top
bills.[status] = '' kann auch im Join stehen, nur Sinn macht es wohl nicht.

Biber hat recht, die Joins scheinen mir auch überflüssig solange keine weiteren WHERE Bedingungen vorliegen oder Daten aus den Tabellen begraucht werden.
Biber
Biber 18.05.2015 aktualisiert um 12:08:43 Uhr
Goto Top
Moin e51bomag,

zu deinem ersten SELECT im Kommentar von 11:50h:

Das geht total in die Grütze, wenn per Zufall bei

 

..  bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid')  
... meinetwegen als das "maximale bills-Datum für Kunde 2 = der 1.5.2015 14:00 ist und wie es der Zufall will noch zwei weitere Kunden genau zu diesem Datum gezahlt haben.
Denn du bekommst ALLE bills-Datensätze mit 1.5.2015 14:00h, auch die vom Kunden 47 und vom Kunden 11 , face-wink

Grüße
Biber
e51bomag
e51bomag 18.05.2015 um 12:21:30 Uhr
Goto Top
Bezüglich der vielen Joins, mir war irgendwie so als müsste ich durch die Tabellen joinen (also durch alle)...eindeutig zu lange raus aus der Materie ;)