[MySQL] GROUP BY - WHERE probleem (geen having)

[OSwimmer]

Hallo iedereen, vanmiddag ben ik op een raar (my)sql probleem gebotst.

Graag zou ik in 1 query het aantal huidige atleten van een groep opvragen. De atleten staan in een tabel en de groepen ook (die dan gelinkt zijn via p.pkIntGroepID = a.fkIntGroepID). Het al dan niet lid zijn van de vereniging (er staan ook voormalig-leden in de tabel) wordt bepaald door een veldje a.blnAtleetLid (0-1). Er zijn echter heel wat groepen die nog geen atleten hebben, daar zou dus gewoon 0 moeten staan.

Als ik volgende query uitvoer, zonder rekening te houden met het al dan niet lid zijn, krijg ik het gewenst resultaat.

sql:

code:

SELECT g.strGroepNaam, count( a.blnAtleetLid ) AS aantal
FROM groep g
LEFT OUTER JOIN atleten a ON a.fkIntGroepID = g.pkIntGroepID
GROUP BY g.strGroepNaam
ORDER BY strGroepNaam

resultaat:

code:

1/2 fond      45
Benjamins              0
fond                      0
joggers                   0
Kadetten / Scholieren   0
Meerkamp    3

Als ik nu echter rekening ga houden met het al dan niet lid, ben ik opeens alle 0-resultaten kwijt.

sql:

code:

SELECT g.strGroepNaam, count( a.blnAtleetLid ) AS aantal
FROM groep g
LEFT OUTER JOIN atleten a ON a.fkIntGroepID = g.pkIntGroepID
WHERE a.blnAtleetLid =1
GROUP BY g.strGroepNaam
ORDER BY strGroepNaam

resultaat:

code:

1/2 fond    22
Meerkamp    3

Heeft iemand enig idee wat ik verkeerd doe?
De MySQL versie die ik run is 4.1.10

[Fulcrum2000]

HAVING a.blnAtleetLid =1 ipv
WHERE a.blnAtleetLid =1 ?

[OSwimmer]

Fulcrum2000 schreef op donderdag 17 maart 2005 @ 19:45:
HAVING a.blnAtleetLid =1 ipv
WHERE a.blnAtleetLid =1 ?

Neen, want bij HAVING ga je een voorwaarde opleggen op je GROUP BY (bij mij dus COUNT[] per groep) instructie, en HAVING a.blnAtleetLidID = 1 is dus ook geen juist sql-syntax

[NMe]

code:

WHERE a.blnAtleetLid =1 OR ISNULL(a.blnAtleetLid)

Kan ook IS_NULL() zijn, of <iets> IS NULL, dat moet je zelf even uitzoeken.

Je vraagt nou gegevens op over een athleet, terwijl die groepen zonder athleet geen gegevens hebben in dat veld, alleen de waarde NULL. Daar moet je dus ook op checken.

[Lustucru]

Werkt ook niet, imho. Het probleem is dat je een criterium zet op atleet en dus zal het resultaat alleen groepen bevatten met altleten die lid zijn. Je moet dus eerst de een deelgroep van de atleten pakken en dan pas outer joinen.
Iets als:

code:

SELECT select a b c
FROM groepen (select q from atleten where lid = 1) 
etc

offtopic:
Mijn van Goor schrijft ook athleet, maar die is wel uit 1946

[ Voor 14% gewijzigd door Lustucru op 17-03-2005 21:41 ]

[NMe]

Niesje schreef op donderdag 17 maart 2005 @ 21:33:
Werkt ook niet, imho. Het probleem is dat je een criterium zet op atleet en dus zal het resultaat alleen groepen bevatten met altleten die lid zijn. Je moet dus eerst de een deelgroep van de atleten pakken en dan pas outer joinen.
Iets als:

code:

SELECT select a b c
FROM groepen (select q from atleten where lid = 1) 
etc

offtopic:
Mijn van Goor schrijft ook athleet, maar die is wel uit 1946

Die van jou gaat misschien wel werken, maar is omslachtig. Zoals ik hem schreef behoort ie te werken, maar mogelijk pas als de WHERE vervangen wordt in een HAVING.

De join geeft voor elk veld uit de eerste tabel alle velden uit de tweede. Daarbij worden niet-bestaande records gewoon aangevuld met NULL-waarden. Dat is ook de reden dat die extra check nodig is.

[Lustucru]

isnull geeft alleen groepen terug die helemaal leeg zijn; een groep die wel leden heeft maar geen der leden is lid vande vereniging valt er genadeloos uit door 'lid=1 or isnull(lid)'. Having voert eerst de query uit en filtert daarna pas het resultaat en is dus ook fout. Just my 2 cents...

[ Voor 6% gewijzigd door Lustucru op 17-03-2005 22:11 ]

[Bananeman]

code:

SELECT g.strGroepNaam, count( a.blnAtleetLid ) AS aantal
FROM groep g
LEFT OUTER JOIN atleten a ON (a.fkIntGroepID = g.pkIntGroepID
                              AND a.blnAtleetLid = 1)
GROUP BY g.strGroepNaam
ORDER BY strGroepNaam

[OSwimmer]

Bananeman2002 schreef op donderdag 17 maart 2005 @ 22:18:

code:

SELECT g.strGroepNaam, count( a.blnAtleetLid ) AS aantal
FROM groep g
LEFT OUTER JOIN atleten a ON (a.fkIntGroepID = g.pkIntGroepID
                              AND a.blnAtleetLid = 1)
GROUP BY g.strGroepNaam
ORDER BY strGroepNaam

Hartelijk dank aan iedereen die me geholpen heeft, en vooral dan aan Bananeman2002 en -NMe- omdat hun oplossing werkt.