✔ SQL View mit Join auf Konto Transactionen

[slimox]

Hallo zusammen,

Ich komme gerade nicht weiter. Ich habe diese zwei Tabellen

Produkte:

CREATE TABLE IF NOT EXISTS `Produkte` (
  `Produkte_id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) NOT NULL,
  `Preis` decimal(10,2) NOT NULL,
  PRIMARY KEY (`Produkte_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `Produkte` (`Produkte_id`, `Name`, `Preis`) VALUES
(1, 'Brot', 6.00),
(2, 'Gurke', 10.00),
(3, 'Nuss', 20.50),
(4, 'Torte', 15.00),
(5, 'Wein', 15.00);

Produkte_verkauf:

CREATE TABLE IF NOT EXISTS `Produkte_verkauf` (
  ` Produkte_verkauf_id` int(11) NOT NULL AUTO_INCREMENT,
  `Produkte_id` int(11) NOT NULL,
  `Geld_bezahlt` decimal(10,2) NOT NULL,
  `Status` smallint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (` Produkte_verkauf_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

INSERT INTO `Produkte_verkauf` (` Produkte_verkauf_id`, `Produkte_id`, `Geld_bezahlt`, `Status`) VALUES
(1, 1, 5.00, 1),
(2, 1, 1.00, 1),
(3, 2, 5.00, 1),
(4, 2, 5.00, 0),
(5, 3, 20.00, 1),
(6, 4, 20.00, 1);

Jetzt möchte ich eine VIEW erstellen. Folgendes sollte in der View zusammengefasst werden:

• Welche Produkte sind bezahlt
• Wieviel wurde bereits bezahlt
• Differenz bereits bezahlt zum Preis

Diesen VIEW SQL habe ich:

CREATE VIEW 
    Produkte_View (
            Produkte_id,
            Name,
            Preis,
            Zahlungsstatus,
            Total_Geld_bezahlt,
            Noch_offen
     ) AS
         SELECT 
            P.Produkte_id, P.Name, P.Preis,
            IF( SUM(V.Geld_bezahlt) >= P.Preis, 1, 0 ) AS Zahlungsstatus,
            SUM(V.Geld_bezahlt) AS Total_Geld_bezahlt,
            (P.Preis - SUM(V.Geld_bezahlt)) AS Noch_offen
         FROM
            Produkte P, Produkte_verkauf V
         WHERE
            V.Produkte_id = P.Produkte_id
         AND
            V.Status = 1
         GROUP BY 
            V.Produkte_id

Gibt diese VIEW:

Produkte_id 	Name 	Preis 	Zahlungsstatus 	Total_Geld_bezahlt 	Noch_offen
	1 	Brot 	6.00 	1 	6.00 	0.00
	2 	Gurke 	10.00 	1 	5.00 	5.00
	3 	Nuss 	20.50 	1 	20.00 	0.50
	4 	Torte 	15.00 	1 	20.00 	-5.00

Diese Punkte sind noch nicht wie gewünscht:

• "Zahlungsstatus" erkennt nicht ob der "Status" in der Tabelle V auf 0=offen oder 1=bezahlt ist
• Produkt "Wein" 5 fehlt ganz, sollte stehen Zahlungsstatus 0 und Noch_offen 15

Wie kann ich die SQL VIEW erweiter, so das diese zwei Punkt auch enthalten sind ?

 

[slimox]

Hat mir niemand eine Idee, was ich falsch mache?

 

[Yaslaw]

Rückfrage: MySQL? Oracle? - ah moment. Du verwendest IF(), das gibts in MySQL also geh ich mal davon aus, es handelt sich um MySQL.

Wie soll der Status mit einbezogen werden?

Um den Wein hineinzukreigen musst du auf ein LEFT JOIN wechseln

SELECT 
   P.Produkte_id, P.Name, P.Preis,
   IF( SUM(V.Geld_bezahlt) >= P.Preis, 1, 0 ) AS Zahlungsstatus,
   SUM(V.Geld_bezahlt) AS Total_Geld_bezahlt,
   (P.Preis - SUM(V.Geld_bezahlt)) AS Noch_offen
FROM
   Produkte P
	LEFT JOIN Produkte_verkauf V
		ON V.Produkte_id = P.Produkte_id
GROUP BY 
   V.Produkte_id

 

[slimox]

Sorry, ja MySQL Der Status sagt aus, ob dieser Betrag auch wirklich bezahlt ist. Wenn 0 = noch nicht bezahlt, also soll dieser Datensatz bei SUM(Geld_bezahlt) nicht mitgerechnet werden Jedoch, habe ich keinen Plan, wenn ich es in der WHERE ausschliesse wo ich es sonst noch ausschliessen muss?!

Danke für dein LEFT JOIN.. ist ja klar!

 

[Yaslaw]

Mit einem einfache IF() auf V.status um damit ggf V.Geld_bezahlt mit 0 zu übersteuern

..
   SUM(IF(V.status, 0, V.Geld_bezahlt)) AS Total_Geld_bezahlt,
..

 

[slimox]

Das mit dem IF habe ich auch schon versucht, hier der SQL:

CREATE VIEW 
    Produkte_View (
            Produkte_id,
            Name,
            Preis,
            Zahlungsstatus,
            Total_Geld_bezahlt,
            Noch_offen
     ) AS
         SELECT 
            P.Produkte_id, P.Name, P.Preis,
            IF(SUM(IF(V.Status, 0, V.Geld_bezahlt)) >= P.Preis, 1, 0 ) AS Zahlungsstatus,
            SUM(IF(V.Status, 0, V.Geld_bezahlt)) AS Total_Geld_bezahlt,
            (P.Preis - SUM(IF(V.Status, 0, V.Geld_bezahlt))) AS Noch_offen
         FROM
            Produkte P
              LEFT JOIN Produkte_verkauf V
                ON V.Produkte_id = P.Produkte_id
         GROUP BY 
            V.Produkte_id

Dies gibt das aus:

5	Wein	15	0	NULL	NULL
1	Brot	6	0	0	6
2	Gurke	10	0	5	5
3	Nuss	20.5	0	0	20.5
4	Torte	15	0	0	15

Das ist aber nicht richtig, bei Produkt_id 1 z.B. gibt es eine Bezahlung 1.- und eine 5.- Total 6.- sollte aber als Status bezahlt sein? Wo ist der Hacken?