[ACCESS]Left Join, SUM nul teruggeven

[Jaspertje]

Allen,

Ben bezig om een wkmanager op te zetten en ben beland bij het punt "tonen van de scores". Ik heb een datamodel waarin de poulewedstrijden, de pouleeindstanden en de verschillende finale rondes in 3 apparte tabellen worden opgeslagen. Nu moet er ook een totaal score getoond worden. Hiervoor had ik de volgende query in gedachte:

SQL:

SELECT U.UserID, U.UserName, U.Betaald, PWV.PuntenAantal As PWV_Punten, PEV.PuntenAantal As PEV_Punten, FLV.PuntenAantal As FLV_Punten, (PWV.PuntenAantal+PEV.PuntenAantal+FLV.PuntenAantal) As punten 
FROM ((Users U 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleWedstrijdVoorspellingen GROUP BY userid) PWV ON U.UserID = PWV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleEindstandVoorspellingen GROUP BY userid) PEV ON U.UserID = PEV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM FinaleLandVoorspellingen GROUP BY userid) FLV ON U.UserID = FLV.UserID 
ORDER BY 6 DESC

Dit gaat niet helemaal goed. Zodra je in alle 3 de tabellen punten hebt, dan telt de query alles perfect op, maar heb je (bijvoorbeeld) nog geen finale's goed, of kan je daar nog geen punten voor hebben, dan blijft FLV.PuntenAantal leeg. Hierdoor gaan alle optellingen ook niet goed en lijkt het alsof je nog helemaal geen punten hebt. Ik heb al geprobeerd om met sum(IsNull("punten, 0 ")) of IsNull("sum(punten), 0") iets aan te passen, maar dat gaat niet goed, want dan blijft het result altijd 0 (ook al zou er wel een ander getal moeten zijn. Ik wil wel graag alle spelers willen tonen in het resultaat en om alles op te tellen in de code en dan opnieuw te sorteren heeft bij mij ook geen voorkeur. Is het mogelijk om te tonen wat ik wil, of moet ik dan wel extra code schrijven?

[P_de_B]

Je moet de IsNull om de PWV.Puntenaantal etc zetten, niet om de sum(punten) in de left gejoinde tabel.

[Jaspertje]

Helaas geeft ook dit overal 0 terug (terwijl er wel punten ingevuld zijn)

[P_de_B]

SQL:

SELECT U.UserID, U.UserName, U.Betaald, ISNULL(PWV.PuntenAantal,0) As PWV_Punten, ISNULL(PEV.PuntenAantal,0) As PEV_Punten, ISNULL(FLV.PuntenAantal,0) As FLV_Punten, (ISNULL(PWV.PuntenAantal,0)+ISNULL(PEV.PuntenAantal,0)+ISNULL(FLV.PuntenAantal,0)) As punten 
FROM ((Users U 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleWedstrijdVoorspellingen GROUP BY userid) PWV ON U.UserID = PWV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleEindstandVoorspellingen GROUP BY userid) PEV ON U.UserID = PEV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM FinaleLandVoorspellingen GROUP BY userid) FLV ON U.UserID = FLV.UserID 
ORDER BY 6 DESC

Bovenstaande query geeft niet het goede resultaat? (let op de ISNULLs ook in de totaaltelling!)

[Jaspertje]

P_de_B schreef op maandag 20 maart 2006 @ 11:34:

SQL:

SELECT U.UserID, U.UserName, U.Betaald, ISNULL(PWV.PuntenAantal,0) As PWV_Punten, ISNULL(PEV.PuntenAantal,0) As PEV_Punten, ISNULL(FLV.PuntenAantal,0) As FLV_Punten, (ISNULL(PWV.PuntenAantal,0)+ISNULL(PEV.PuntenAantal,0)+ISNULL(FLV.PuntenAantal,0)) As punten 
FROM ((Users U 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleWedstrijdVoorspellingen GROUP BY userid) PWV ON U.UserID = PWV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleEindstandVoorspellingen GROUP BY userid) PEV ON U.UserID = PEV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM FinaleLandVoorspellingen GROUP BY userid) FLV ON U.UserID = FLV.UserID 
ORDER BY 6 DESC

Bovenstaande query geeft niet het goede resultaat? (let op de ISNULLs ook in de totaaltelling!)

Helaas geeft ook dit overal 0 terug (terwijl er wel punten ingevuld zijn) Dit geeft de eerste query die ik poste terug:

UserID	UserName	Betaald	PWV_Punten	PEV_Punten	FLV_Punten	punten
5	User1	Ja	6	10	40	56
6	User2	Nee			10

Dit geeft de query terug zoals P_de_B die neerzet, met alleen een IsNull("PWV.PuntenAantal,0") bij de eerste PWV.PuntenAantal (zelfde gegevens):

SQL:

SELECT U.UserID, U.UserName, U.Betaald, IsNull("PWV.PuntenAantal,0") As PWV_Punten, PEV.PuntenAantal As PEV_Punten, FLV.PuntenAantal As FLV_Punten, (PWV.PuntenAantal+PEV.PuntenAantal+FLV.PuntenAantal) As punten 
FROM ((Users U LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleWedstrijdVoorspellingen GROUP BY userid) PWV ON U.UserID = PWV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM PouleEindstandVoorspellingen GROUP BY userid) PEV ON U.UserID = PEV.UserID) 
LEFT JOIN (SELECT userid, sum(punten) As PuntenAantal FROM FinaleLandVoorspellingen GROUP BY userid) FLV ON U.UserID = FLV.UserID 
ORDER BY 6 DESC

UserID	UserName	Betaald	PWV_Punten	PEV_Punten	FLV_Punten	punten
5	User1	Ja	0	10	40	56
6	User2	Nee	0		10

Access vindt dat alle die waardes dan NULL zijn (terwijl er wel een getal in staat)
_{in acces moet je in de isnull("") " neerzetten}

[ Voor 3% gewijzigd door Jaspertje op 20-03-2006 11:42 ]

[Dido]

En als je alle isnull's nu gebruikt? Anders heeft het weinig zin

P_de_B geeft je een query, je neemt die maar gedeeltelijk over, en zegt dattie niet werkt...

[ Voor 42% gewijzigd door Dido op 20-03-2006 11:48 ]

[P_de_B]

Even in de help gekeken, en het blijkt dat de Access IsNull functie afwijkt van de SQL Server IsNull functie. Hij geeft TRUE/FALSE terug, en niet zoals bij SQL Server zijn tweede argument als de eerst null is.

Je moet IIF in combinatie met IsNull gebruiken.

SQL:

IIf(IsNull([Veld]), 0, [Veld])

[Jaspertje]

Dido schreef op maandag 20 maart 2006 @ 11:47:
En als je alle isnull's nu gebruikt? Anders heeft het weinig zin

P_de_B geeft je een query, je neemt die maar gedeeltelijk over, en zegt dattie niet werkt...

Uitteraard heb ik dat geprobeerd, maar het enige wat ik dan terug krijg is allemaal 0-waardes. Het bovenstaande voorbeeld was alleen om aan te geven dat het niet werkt (dat voorbeeld had ik beter uit kunnen breiden met alle isnull() waardes.

[Lustucru]

Zo'n iif() heet in access nz().

code:

nz(var,[OptioneleWaardeIndienNull])

geeft var terug indien niet null, anders 0,"", of de waardeindiennull, als die meegegeven is tenminste.

[Jaspertje]

P_de_B schreef op maandag 20 maart 2006 @ 11:47:
Even in de help gekeken, en het blijkt dat de Access IsNull functie afwijkt van de SQL Server IsNull functie. Hij geeft TRUE/FALSE terug, en niet zoals bij SQL Server zijn tweede argument als de eerst null is.

Je moet IIF in combinatie met IsNull gebruiken.

SQL:

IIf(IsNull([Veld]), 0, [Veld])

Thats gonne be it.. dank

Niesje schreef op maandag 20 maart 2006 @ 11:53:
Zo'n iif() heet in access nz().

code:

nz(var,[OptioneleWaardeIndienNull])

geeft var terug indien niet null, anders 0,"", of de waardeindiennull, als die meegegeven is tenminste.

Nz zit niet in Access Projects oid (dat was ik ook al tegen gekomen, zoek nog even naar de link)
http://www.aspfaq.com/show.asp?id=2394 (Nz ism VBScript, ik ben bezig in ASP, vandaar)

[ Voor 42% gewijzigd door Jaspertje op 20-03-2006 12:00 ]

[P_de_B]

hmm, in de help staat eerst een hele uitleg over hoe het te doen met IIF, geven ze onderaan de NZ functie zover had ik niet gekeken.

[Jaspertje]

Wil nog een kleine aanvulling doen: Je kan ook op de sum heen een IsNull() zetten (ism Iif) Hierdoor hoef je het niet meerdere keren in je Select te doen (zoals bij mij). Of het sneller is, weet ik niet, maar het scheelt wel Iif() functie's in je statement