[SQL] Join voorwaarde

[Goegol]

Momenteel ben ik bezig met een webapplicatie te ontwikkelen.
Het wordt een soort online orderprogramma.
Daarin wordt de btw ook bijgehouden over de verkochte artikelen.

Om de btw te berekenen heb ik de volgende sql statement gemaakt:

SQL:

 
SELECT artikelnummer, artikelnaam, marge, btwpercentage, inkoopprijs, SUM(orderregel.aantal) as aantal
FROM orderregel
INNER JOIN orders
USING (ordernummer)
INNER JOIN artikel
USING (artikelnummer)
INNER JOIN btw
USING (btwnummer)
INNER JOIN leveringregel
USING (artikelnummer)
WHERE orders.orderdatum BETWEEN '2009-01-01'
AND '2009-12-31'
GROUP BY artikelnummer

Dit werkt goed.
Alleen omdat de btw weleens wil wijzigen hebben we een tabel waaruit de de btw gegevens lezen en kunnen aanpassen. Daar hebben we de volgende tabel voor:

SQL:

CREATE TABLE(
  btwnummer int(10) NOT NULL,
  ingangsdatum date,
  btwomschrijving varchar(20),
  btwpercentage double,
  PRIMARY KEY  (BTWnummer,ingangsdatum)
);

Alle producten wordt aan een btwnummer gekoppeld.
Momenteel hebben we 2 btwnummers namelijk:

• 1 food
• 2 non-food

Ons idee is als de btw wijzigd dat we de ingangsdatum invullen en het nieuwe percentage.

Nu het probleem:
Hoe geef ik in de join mee welke percentage moet worden uitgelezen om de btw te berekenen?

Ik heb zelf al zitten proberen met de where statement maar dan geeft hij alle producten bijvoorbeeld de nieuwe btwpercentage mee ipv van de order waarvan de orderdatum na de invoerdatum van de btwpercentage ligt.

Hopelijk kunnen jullie me helpen!

[T-MOB]

Je moet het btw-percentage dat van toepassing is opslaan bij je orderregel. Je kunt dat doen door een koppeling te maken, maar het is net zo handig om gewoon het percentage op te slaan. Het percentage op verstuurde orders verandert immers nooit meer.

[cariolive23]

GROUP BY artikelnummer

En waar zijn alle andere kolommen waar je op MOET groupen? De huidige query is fout en gaat gokken welke resultaten je vandaag zou willen zien. Ga je MySQL-server (runtime) configureren en zorg er voor dat soort queries direct een foutmelding opleveren, het zijn tenslotte foute queries. Erg leuk wanneer een webshop maakt, maar niet wanneer er niets van de boekhouding klopt.

[Goegol]

@T-MOB: thnx voor de oplossing!!
@cariolive23: hoezo is deze query fout? Waarom moet ik op andere kolommen grouperen? welke andere kolommen moet ik grouperen?

[TeeDee]

Goegol schreef op woensdag 02 september 2009 @ 23:48:
@T-MOB: thnx voor de oplossing!!
@cariolive23: hoezo is deze query fout? Waarom moet ik op andere kolommen grouperen? welke andere kolommen moet ik grouperen?

artikelnummer, artikelnaam, marge, btwpercentage, inkoopprijs dienen ook in de GROUP BY te staan.

Bijvoorbeeld bij MS SQL krijg je de melding dat "Column ... is invalid in the select list because it is not contained either in an Aggregrate function or the GROUP BY Clause.

MySQL, zoals cariolive23 aangeeft, gokt de rest erbij. Imo mag je daar niet op vertrouwen., zeker als het om bijvoorbeeld een boekhoudings/order pakket gaat.

[ Voor 0% gewijzigd door TeeDee op 03-09-2009 00:35 . Reden: I stand corrected ZaZ ;) ]

[ZaZ]

TeeDee schreef op donderdag 03 september 2009 @ 00:00:
[...]
Imo mag je daar niet op vertrouwen, zeker als het om bijvoorbeeld een boekhoudings/order pakket gaat.

Je bent te bescheiden. Maakt geen drol uit waar het voor gebruikt wordt, zolang je met iets anders dan je kont naar het resultaat kijkt. Is net zoiets als een rekenmachine vragen wat de uitkomst van 2 + 3 is en het antwoord is "we gooien een dobbelsteen op!"

[Gomez12]

Wat is er mis met een btw-percentage 3 - food ( na xxxx-xx-xx )?

Wat een leuk voorbeeld is van wat cariolive bedoelt ( en wat nog wat andere leukigheidjes naar boven haalt ) is als je 1 artikel 2x op 1 order hebt staan ( vb. je hebt een actie waarbij je bij aanschaf van een printer er 1 toner gratis bijlevert en de klant bestelt gelijk nog 3 toners erbij )

[cariolive23]

Goegol schreef op woensdag 02 september 2009 @ 23:48:
@cariolive23: hoezo is deze query fout? Waarom moet ik op andere kolommen grouperen? welke andere kolommen moet ik grouperen?

Het is fout omdat het wiskundig helemaal niet mogelijk is. Je bent bezig met ee aggregate functie, je wilt dus eigenschappen opvragen van een groep records. Je kunt dan niet meer de eigenschap van één enkel record opvragen, deze eigenschap is namelijk géén eigenschap van de complete groep.

Voorbeeldje:
Wat is het gemiddelde (aggregate functie!) banksaldo van alle mensen met blond haar en wat is hun schoenmaat?

Jouw antwoord: 2500 euro, maat 42.

Die 2500 euro kan nog wel eens kloppen (kun je gewoon berekenen), maar die schoenmaat klopt van geen meter. Je gaat mij niet wijsmaken dat iedereen uit de groep met blond haar óók schoenmaat 42 heeft. Het feit dat opgroeiende kinderen regelmatig een andere schoenmaat hebben, zegt genoeg. Kortom, de vraag is fout en had met een dikke foutmelding afgeschoten moeten worden. Maar ja, MySQL.... Onbetrouwbare rommel die je alleen met de nodige aanpassingen in de (runtime) configuratie wat betrouwbaarder kan krijgen.

http://wiki.phpfreakz.nl/Werken_met_MySQL#Configuratie

Onthou wel dat iedereen de configuratie kan aanpassen (deze rechten kun je niet intrekken) en dat het dus altijd fout kan gaan.

Sterkte met je administratie.

[Woy]

Goegol schreef op woensdag 02 september 2009 @ 23:48:
@cariolive23: hoezo is deze query fout? Waarom moet ik op andere kolommen grouperen? welke andere kolommen moet ik grouperen?

Zie onze FAQ: Hoe werkt dat GROUP BY nu eigenlijk?

Het is een veel gemaakte fout onder MySql gebruikers, maar met GROUP BY moeten alle velden die niet i.c.m. een aggregate function gebruikt worden, in de GROUP BY clause vermeld worden. De reden daarvoor legt cariolive23 hierboven idd al uit. Dat MySql wat anders accepteert is gewoon fout, al vinden de ontwikkelaars van MySql dat blijkbaar anders.

[Janoz]

cariolive23 schreef op donderdag 03 september 2009 @ 09:17:
[...]

Het is fout omdat het wiskundig helemaal niet mogelijk is. Je bent bezig met ee aggregate functie, je wilt dus eigenschappen opvragen van een groep records. Je kunt dan niet meer de eigenschap van één enkel record opvragen, deze eigenschap is namelijk géén eigenschap van de complete groep.

Voorbeeldje:
Wat is het gemiddelde (aggregate functie!) banksaldo van alle mensen met blond haar en wat is hun schoenmaat?

Jouw antwoord: 2500 euro, maat 42.

Die 2500 euro kan nog wel eens kloppen (kun je gewoon berekenen), maar die schoenmaat klopt van geen meter. Je gaat mij niet wijsmaken dat iedereen uit de groep met blond haar óók schoenmaat 42 heeft.

Om dit toch leuke voorbeeld even iets te verduidelijken. Deze situatie heeft twee oplossingen
1: Pas een aggregerende* functie toe op schoenmaat. Denk daarbij aan gemiddelde, maximale, minimale. Dat betekend dus dat je de vraag aanpast naar 'en wat is hun gemiddelde/maximale/minimale schoenmaat.
2: Verdeel de resultaten per schoenmaat door schoenmaat aan de group by toe te voegen. Je krijgt dan het gemiddelde banksaldo per schoenmaat. Waardoor je dus ook de vraag aanpast naar 'per schoenmaat'.

_{Een aggregerende functie is een functie die een verzameling waarden als input heeft en als resultaat vervolgens 1 waarde heeft. Het brengt dus een hele set terug naar 1 waarde. Let daarbij op dat de uitkomst helemaal niet 1 van de input waarden hoeft te zijn. Dat lijkt wat triviaal, maar dat wordt door veel mensen vergeten wanneer ze in MySQL denken een query te kunnen maken waarbij ze MAX gebruiken.}

[ Voor 5% gewijzigd door Janoz op 03-09-2009 10:35 ]

[Verwijderd]

Tsja, van de andere kant gaan de MySQL-devvers er misschien van uit dat de ontwikkelaar dat eigenlijk dient te weten. Maar, al met al, het is fout, en eigenlijk zou er dus een melding gegeven moeten worden. Zoals gezegt zorgt het voor 'onvoorspelbare' resultaten.

[cariolive23]

Verwijderd schreef op donderdag 03 september 2009 @ 10:35:
Tsja, van de andere kant gaan de MySQL-devvers er misschien van uit dat de ontwikkelaar dat eigenlijk dient te weten.

Je weet het mooi te brengen, je bent extreem positief

Maar, al met al, het is fout, en eigenlijk zou er dus een melding gegeven moeten worden. Zoals gezegt zorgt het voor 'onvoorspelbare' resultaten.

"onvoorspelbaar" mag je hier vervangen door "onbruikbaar", je hebt er niets aan. Het antwoord dat je krijgt, is namelijk fout, tenzij er toevallig goed is gegokt. Maar dat kun je niet controleren, je kunt dus niet aannemen dat het antwoord goed is. En met dit soort gedrag van jouw database wil je een administratie maken?

Mijn ervaring leert mij dat 9 van de 10 applicaties met MySQL-databases dit soort fouten in hun code hebben zitten. Deze fouten zijn het snelst te vinden door MySQL zo te configureren dat foute queries en foute data wordt afgekeurd, de applicaties gaan dan keurig één voor één op hun bek. Mocht er een DBA aan hebben meegewerkt, vraag hem/haar dan eerst even naar de configuratie, dan krijg je vaak al een aardig idee van de kwaliteit. Zonder DBA is het eigenlijk een kansloos verhaal, de standaard configuratie van MySQL klopt gewoon van geen kanten, is totaal onbruikbaar.