SQL query controleren

[stokj]

Wie kan deze query controleren of hij goed is:

De opdracht luidt om een view te maken waarin de kolommen klantnr, klantnaam, bestelnr, besteldatum, artikelcode en aantal te zien zijn.
Tabel Klant bevat klantnr, klantnaam
Tabel Bestelling bevat bestelnr, besteldatum, klantnr (verwijzende sleutel naar Klant.klantnr)
Tabel Artikel bevat artikelcode
Tabel BesteldArtikel bevat bestelnr (verwijzende sleutel naar Bestelling.bestelnr), artikelcode (verwijzende sleutel naar Artikel.artikelcode)

Dit is wat ik denk dat de query moet zijn:

CREATE VIEW Overzicht AS
SELECT *
FROM Klant, Bestelling, Artikel, BesteldArtikel

De view roep je dan aan met:
SELECT klantnr, klantnaam, bestelnr, besteldatum, artikelcode, aantal
FROM Overzicht

Volgens mij mag je in een view geen join gebruiken?

[WelVrolijk]

Ik zou geneigd zijn, in een view wel degelijk joins te gebruiken.

Mocht vervolgens blijken dat het systeem dat niet aan kan, dan kun je het altijd nog (zonder enige moeite) omschrijven naar een select zonder joins.
Door de joins wordt het makkelijker leesbaar.
- Daardoor wordt de query makkelijker te onderhouden.
- Ook wordt het voor je collega's (of je chef) dan makkelijker, de query te reviewen. Dat komt dus de kwaliteit ten goede.
- En de business kan daardoor de query beter beoordelen. Dat vergroot dus de kans dat de klant het systeem accepteert, en vervroegt dus het moment dat jouw werk betaald wordt.

-----------------

De view die jij gescreven hebt, zal niet het gewenste resultaat hebben.

De gebruiker wil bij een bestelling namelijk alleen de naam zien van de klant die bij die bestelling hoort.
Bij jouw query zal de gebruiker echter *alle* klanten te zien krijgen.
Hetzelfde geldt voor de artikelen.
De klant krijgt een carthetisch product te zien!


Stel bijvoorbeeld dat je 20 artikelen hebt, en 10 klanten, en er komen 5 bestellingen binnen van gemiddeld 3 artikelen.
Dan wil de gebruiker 15 regels zien als resultaat van de query.
Maar bij jouw query ziet hij (20*10*5*15 = ) 15.000 regels ...


Bij een bepaald BesteldArtikel, wil de gebruiker niet alle 20 artikelen zien, maar alleen dat ene artikel dat bij die regel hoort.
Bij een bepaald BesteldArtikel, wil de gebruiker niet alle 5 bestellingen zien, maar alleen dat die ene bestelling waar die regel bij hoort.
Bij een bepaald BesteldArtikel, wil de gebruiker niet alle 10 klanten zien, maar alleen die ene klant die die bestelling heeft gedaan.

[WelVrolijk]

Overigens:

Als je alleen maar de kolommen klantnr, klantnaam, bestelnr, besteldatum, artikelcode en aantal nodig hebt, heb je tabel Artikel helemaal niet nodig ...

[stokj]

Dus als ik het goed begrijp mag je in een view wel degelijk gebruik maken van joins.

In welk stuk mag dat dan precies, want als je een CREATE VIEW doet, moet je deze aanroepen met SELECT ..... FROM <viewnaam>.

In welk gedeelte mag dan een join komen? in de CREATE VIEW of aanroep quey?

[WelVrolijk]

Die join stop je in de CREATE VIEW.


Dan krijg je iets als:


CREATE VIEW ArtikelInfo AS
SELECT art.artikelcode, art.omschrijving AS artikelnaam, art.productgroepcode, grp.omschrijving AS productgroepnaam
FROM Artikel art
JOIN ProductGroep grp ON grp.productgroepcode = art.productgroepcode;
Het kan zijn dat er een paar haakjes om de SELECT-clause moeten. En bij sommige systemen moet je per se een spatie voor de puntkomma zetten.
Maar hoe dat in jouw situatie precies moet kun je opzoeken in de documentatie (de grammatica), of je kunt het even uitproberen (afhankelijk van de situatie).

En bij de aanroep krijg je dan bijvoorbeeld:

select artikelcode, artikelnaam, productgroepnaam
from ArtikelInfo
where artikelcode = 9012345;

of

select *
from ArtikelInfo
where productgroepcode = 123;

[stokj]

Dus als ik het goed begrijp moet het zoiets worden zoals hieronder staat. Met zeg maar klantnr en bestelnr als join, omdat die verwijzende sleutels zijn.

CREATE VIEW Overzicht AS
SELECT k.klantnr, k.klantnaam, b.bestelnr, b.besteldatum, a.artikelcode, ba.aantal
FROM Klant k, Bestelling b, Artikel a, BesteldArtikel ba
WHERE k.klantnr = b.klantnr
AND b.bestelnr = ba.bestelnr

SELECT *
FROM Overzicht

stokj schreef op 18-05-2007 @ 11:54 :
Dus als ik het goed begrijp moet het zoiets worden zoals hieronder staat. Met zeg maar klantnr en bestelnr als join, omdat die verwijzende sleutels zijn.

CREATE VIEW Overzicht AS
SELECT k.klantnr, k.klantnaam, b.bestelnr, b.besteldatum, a.artikelcode, ba.aantal
FROM Klant k, Bestelling b, Artikel a, BesteldArtikel ba
WHERE k.klantnr = b.klantnr
AND b.bestelnr = ba.bestelnr

SELECT *
FROM Overzicht

In principe is dat heel goed.

Maar je hebt de tabel Artikel opgenomen in de query.
Dan zul je daar dus ook een JOIN-conditie voor moeten toevoegen (anders krijg je alsnog een carthesisch product).

(In de praktijk lijkt het me ook wel degelijk verstandig, de Artikel tabel mee te nemen in de view. Want vroeg of laat zal de klant een van de velden van die tabel willen raadplegen.)

-----------------

Toch nog twee opmerkingen:

1. Ik denk, dat je het best bij de aanroep van de view *ook* precies de gewenste velden kunt aangeven. Dan blijft de query in ieder geval op dezelfde manier werken, als iemand een veld toevoegt aan de view.

2. In de praktijk kan het handig zijn, een paar velden meer op te nemen in de view. Maar dat mag je als ontwikkelaar of ontwerper in principe niet zelf beslissen, je zult dus expliciet toestemming moeten vragen aan je opdrachtgever.
Deze extra velden kunnen het voor jou (of voor je concurrent!) later makkelijker maken, als er nieuwe rapportages nodig zijn. Maar de klant betaalt voor het extra werk, en de klant betaalt voor de extra verwerkingskosten, dus de klant heeft hierin het laatse woord.

[stokj]

Beetje bij beetje begin ik het beter te begrijpen.

Dit is de query die ik nu heb:


CREATE VIEW Overzicht AS
SELECT k.klantnr, k.klantnaam, b.bestelnr, b.besteldatum, a.artikelcode, ba.aantal
FROM Klant k, Bestelling b, BesteldArtikel ba LEFT JOIN Artikel a ON BesteldArtikel.artikelcode = Artikel.artikelcode
WHERE k.klantnr = b.klantnr
AND b.bestelnr = ba.bestelnr

SELECT *
FROM Overzicht


Klopt het nu zo? Hoe moeten de tabellen in de join verwijzen? is het heerst oudertabel en dan kindtabel die genoteerd moet worden of kindtabel-oudertabel?

[WelVrolijk]

Formeel gesproken klopt jouw query.

Een probleem is echter, dat je twee van de 3 joins in een WHERE conditie stopt, en een van de drie in een JOIN clause.

Het is duidelijker (en dus beter) als je
- ofwel alle 3 joins in de WHERE clause stopt
- ofwel alle 3 joins in een JOIN clause.

Een voordeel van het gebruik van de JOIN clause, is dat je het onmidellijk merkt als je een conditie vergeet.

--------

Voor de volgorde van de tabellen in de JOIN ga ik doorgaans uit van wat het makkelijkst te begrijpen is. (Het DBMS zal hoe dan ook proberen de boel om te gooien om de query efficienter te maken, en dat zal doorgaans beter lukken naarmate de query eenvoudiger geformuleerd is).

In dit geval verwacht ik dat een gebruiker doorgaans vooral geinteresseerd is in de gegevens van een bestelling.
Enerzijds wil je dan weten wat de klant is (dus je joint er de klant tabel bij), en anderzijds wil je de bestelregels zien en de artikelen (dus je joint er achtereenvolgens het BesteldArtikel en het Artikel bij).

In sommige branches is de gebruiker echter meer geinteresseerd in alle bestelregels die er (bijvoorbeeld) nog niet geleverd zijn.
Voor zo'n bedrijf zou ik dus beginnen bij de klant, en er dan achterrenvolgens de bestelling, het besteldartikel en het artikel aan toevoegen.

Mogelijk bestaan er zelfs branches waarin de vraag zo groot is dat vrijwel alles vanuit backorder geleverd wordt. In dat geval zou je dus (zodra er bericht binnenkomt dat er een lading binnenkomet van een bepaald artikel) juist uitgaan van het artikel, en daar de andere tabellen aan joinen.

Als het goed is, "weet" het DBMS hoe de tabellen doorgaans gebruikt worden en hoe groot ze zijn, zodat in de praktijk de volgorde waarin je die tabellen joint (vrijwel) geen invloed zou hebben op de performance.
De volgorde waarin je de tabellen joint heeft echter een grote invloed op de leesbaarheid van je query, en dus op het gemk waarmee je query beoordeeld kan worden, en dus ook op de beoordeling van jouw query.

Kortom: Kijk nog even goed hoe de vraag precies is gesteld. Herschrijf je query zo, dat hij zo goed mogelijk aansluit bij de vraag. Check tenslotte (indien mogelijk) of de query nog steeds hetzelfde resultaat oplevert.

[stokj]

Dan moet dit em zijn:

CREATE VIEW Overzicht AS
SELECT k.klantnr, k.klantnaam, b.bestelnr, b.besteldatum, a.artikelcode, ba.aantal
FROM Klant k LEFT JOIN Bestelling b ON Klant.klantnr = Bestelling.klantnr, BesteldArtikel ba LEFT JOIN Artikel a ON BesteldArtikel.artikelcode = Artikel.artikelcode,
Bestelling b LEFT JOIN BesteldArtikel ba ON Bestelling.bestelnr = BesteldArtikel.bestelnr

View aanroep:
SELECT *
FROM Overzicht

[WelVrolijk]

stokj schreef op 25-05-2007 @ 17:47 :
Dan moet dit em zijn:

CREATE VIEW Overzicht AS
SELECT k.klantnr, k.klantnaam, b.bestelnr, b.besteldatum, a.artikelcode, ba.aantal
FROM Klant k LEFT JOIN Bestelling b ON Klant.klantnr = Bestelling.klantnr, BesteldArtikel ba LEFT JOIN Artikel a ON BesteldArtikel.artikelcode = Artikel.artikelcode,
Bestelling b LEFT JOIN BesteldArtikel ba ON Bestelling.bestelnr = BesteldArtikel.bestelnr

View aanroep:
SELECT *
FROM Overzicht

Bijna goed.

Ik vrees alleen dat deze query grammaticaal gezien niet klopt.
Dus als je hem zo intikt, krijg je een foutmelding.

Functioneel gezien is het (volgens mij) nu in orde.
En dat is in de praktijk het belangrijkste;
- Als je de query zo intikt, zullen de foutmeldingen jou helpen bij het oplossen van de syntaxfouten, en dan kom je uiteindelijk op de goede query uit.
- Als je de query zo aflevert (in een werk-situatie), zal een van je collega's je tijdens een peer review helpen bij het oplossen van de syntaxfouten, en dan kom je uiteindelijk op de goede query uit.
- Als je de query zo inlevert (in een onderwijs-situatie), krijg je puntenaftrek vanwege de syntaxfout. Maar de docent zal zien dat je begrijpt wat je moet doen, dus als het goed is krijg je daar wel weer punten voor.

--------------------------

De juiste syntax hangt af van de versie van SQL die jullie gebruiken.

Blijkbaar gebruiken jullie:
FROM
tabel1 alias1
LEFT JOIN tabel2 alias 2 ON alias1.veld1 = alias2.veld2
om twee tabellen te joinen.

Het resultaat van zo'n join beschouwen we (in een relationele database) weer als een tabel.
Om daar een derde tabel aan toe te voegen, zou je dus eigenlijk het volgende willen doen:
FROM
<resultaat1> alias1a
LEFT JOIN tabel3 alias3 ON alias1a.veld3 = alias3.veld2
Dan moet je (in de SQL-versie waar ik mee werk) op de plaats van dat resultaat dus gewoon je vorige join neerzetten:
FROM
tabel1 alias1
LEFT JOIN tabel2 alias 2 ON alias1.veld1 = alias2.veld2
LEFT JOIN tabel3 alias3 ON alias2.veld1 = alias3.veld1
Bij die tweede JOIN beschouw je dan gewoon alles wat ervoor staat als een tabel, die je joint met de tabel die onmiddellijk achter LEFT JOIN staat.
Op dezelfde manier kun je dan vervolgens de vierde tabel er aan joinen.

Veel succes!