Al een tijdje ben ik bezig om een uitgebreide MySQL query te maken. Ik weet alleen niet hoe ik het goed krijg en ik hoop dat iemand mij ermee kan helpen. Ik heb 2 tabellen in de database staan

Tabel: "bon"
Hierin staan de bonnen van verkochte producten. Status 1 betekend dat de bon betaald is.

id	datum	prijs	status
1	27-04-2017	100.00	1
2	28-04-2017	95.86	0
3	28-04-2017	197.45	0
4	29-04-2017	36.98	1

Tabel: "retour"
Hierin staan producten die retour gebracht zijn.

id	datum	prijs
1	24-04-2017	20.00
2	24-04-2017	37.00
3	27-04-2017	198.00
4	29-04-2017	129.36

Wat ik uiteindelijk als output wil hebben:
Per dag wil ik zien wat er open staat, wat er betaald is, wat retour is gebracht en uiteindelijk de winst (open + betaald - retour bedrag). Als er op een dag geen bonnen of retour zijn wil ik wel dat deze dag in de tabel komt te staan met 0 erin.

Datum	Open	Betaald	Retour	Winst
24-04-2017	100.00	534.89	20.49	614.40
25-04-2017	238.95	429.95	30.95	637.95
26-04-2017	95.45	756.90	175.99	676.36
27-04-2017	0	0	0	0
28-04-2017	28.45	295.95	0	324.40

De gebruik kan zelf al een maand + jaar kiezen vanuit een dropdown. Deze gegevens heb ik in de variable $month & $year staan.

Wat ik nu heb is onderstaande query maar hiermee doe ik niks met de retour gebrachte artikelen en zit er maar 1 status in de query. Op dit moment heb ik 2 aparte tabellen, openstaande bonnen en betaalde bonnen maar dit wil ik eigenlijk in 1 tabel krijgen.

SELECT DAY(datum), SUM(`prijs`) as prijs_totaal, datum FROM `bon` WHERE YEAR(datum) = $year AND MONTH(datum) = $month AND status=1 GROUP BY YEAR(datum), MONTH(datum), DAY(datum)


Ik hoop dat iemand mij kan helpen met de query waarmee ik deze gegevens mooi in 1 tabel kan krijgen.

Waarom wil je dit perse vanuit een query doen? Dit zou veel efficiënter, leesbaarder én onderhoudbaarder zijn als je dit vanuit de code opbouwt.

Als eerste kun je voor die datum al een BETWEEN gebruiken en de query een stuk sneller te maken door niet 2 functies voor ELK record in de db uit te voeren.
Dan kun je met de mysql constructie: "if(status = 1, prijs, 0) as verkocht, if(status = 0, prijs, 0) as open" al de verkocht/open velden selecteren per dag. Dit zijn in principe ook 2 functies (veel sneller dan een YEAR() of MONTH()) en werken alleen op de resultset die aan de where voldoet. Veel minder vaak dus.

In principe kun je met een subselect ook nog het retour bedrag erbij halen, maar dat betekent een per definitie niet te cachen query voor het aantal dagen dat er bonnen zijn. Dus dat is duf.

Dan kun je beter een enkele select doen om de retour bedragen van die maand op te halen. Dit zijn max 31 rows in 1 keer die perfect gebruik kunnen maken van de index op datum.

De tabel opbouwen doe je inderdaad via php omdat je daar in een loopje beiden resultsets uitleest en de dagen die in beide tabellen niet voorkomen 'static' in je tabel toevoegt.

Even vlug wat gemaakt om mijn MySQL skills te testen. Dit geeft alleen de dagen terug waar resultaat op is geweest. Ik kan eventueel ook de dagen terug laten komen door een temptable bij te zetten, met alle waardes op 0, maar dat vond ik voor nu even te veel werk. Ik denk dat je wel snap waarom ik het advies gaf om dit in code te doen. :)

PHP Code:

SELECT 
    d.Datum, 
    SUM(d.Open) As Open
    , SUM(d.Betaald) As Betaald
    , SUM(d.Retour) As Retour
FROM ((SELECT retour.datum as Datum
    , 0 As Open
    , 0 As Betaald
    , (
        SELECT IFNULL(SUM(retour2.prijs),0) 
        FROM retour as retour2 
        WHERE retour2.datum = retour.datum
        ) As Retour
    FROM retour)
    UNION ALL
    (SELECT bon.datum as Datum
        ,(
            SELECT IFNULL(SUM(bon2.prijs),0)
            FROM bon AS bon2
            WHERE bon2.datum = bon.datum
                AND status = 0
            ) AS Open
        ,(
            SELECT IFNULL(SUM(bon2.prijs),0)
            FROM bon AS bon2
            WHERE bon2.datum = bon.datum
                AND status = 1
            ) AS Betaald
        ,0 as Retour
FROM bon)) as d
GROUP BY Datum 


Hartstikke bedankt voor de reacties en tips! Danny ik ga zo je code proberen, ik laat het je weten of het hiermee gelukt is.

Ik raad eigenlijk niet aan om deze te gebruiken. Het was voornamelijk om te laten zien hoe complex een dergelijke query is. Dit soort queries hebben heel snel de neiging om traag te worden. Voer 2 simpele queries uit en bouw het gewenste resultaat op vanuit de code. Dat is de beste manier om hier mee om te gaan.