[MySQL] Sorteren op COUNT, maar met counts van 0 erbij.

[OverSoft]

Ik heb hier (als voorbeeld) even 2 tabellen:

Tabelnaam: groep
id: int(11) PRI
groep: char(64)

Tabelnaam: users
id: int(11) PRI
user: char(64)
groep: int(11) PRI

Nou wijs ik dus aan de users een groep toe. De groep(int) in de tabel users wijst naar de id van de tabel groep. Ik heb een aantal groepen, echter zijn sommigen ook leeg (zitten nog geen users in).

Nou wil ik de volgende query uitvoeren:

SQL:

SELECT COUNT(a.id) AS aantal, b.groep FROM users a, groep b WHERE a.groep = b.id GROUP BY groep ORDER BY aantal DESC;

Deze query checkt hoeveel users er in een bepaalde groep zitten en sorteer deze op aantal.
Dit werkt allemaal goed, alleen neemt deze niet de lege groepen mee (logisch, want die zitten ook niet bij users). Nou heb ik al allerlei verschillende queries geprobeerd, maar ik kom er maar niet uit.

Is er een mogelijkheid om lege groepen mee te nemen in deze query?
Graag geen PHP code, o.i.d., ik wil dit puur in SQL doen.

Voorbeeld van wat ik heb geprobeerd:

SQL:

SELECT b.groep, COUNT(a.id) AS aantal FROM users a, groep b WHERE a.groep IN (SELECT id FROM groep;) GROUP BY a.groep ORDER BY aantal DESC;

[Verwijderd]

Met een leftjoin

Is die users.groep geen Foreign Key trouwens...?

Zo dus

SQL:

SELECT COUNT(a.id) AS aantal, b.groep FROM users a LEFT JOIN groep b ON a.groep = b.id GROUP BY groep ORDER BY aantal DESC;

[ Voor 60% gewijzigd door Verwijderd op 03-04-2006 16:18 ]

[Dido]

Kijk eens naar LEFT JOIN

dus

code:

SELECT veld
FROM tabel1 a LEFT JOIN tabel2 b ON a.id = b.id

[ Voor 3% gewijzigd door Dido op 03-04-2006 16:19 ]

[OverSoft]

Verwijderd schreef op maandag 03 april 2006 @ 16:17:
Met een leftjoin

Is die users.groep geen Foreign Key trouwens...?

Zo dus

SQL:

SELECT COUNT(a.id) AS aantal, b.groep FROM users a LEFT JOIN groep b ON a.groep = b.id GROUP BY groep ORDER BY aantal DESC;

Hmh, ik ben niet bekend met Foreign keys, zal er eens naar gaan kijken (mijn SQL is een klein beetje rusty).
Overigens, code net even getest, krijg helaas nog geen 0 groepen erbij

/edit:
Met een RIGHT JOIN werkt ie wel

[ Voor 5% gewijzigd door OverSoft op 03-04-2006 16:23 ]

[Dido]

left en right werken hetzelfde, maar met een left krijg je alles uit de linker tabel, plus wat er matched uit de rechter, met een right join krijg je alles uit de rechter en wat er matched uit de linker.

Het hoeft niet, maar zelf heb ik een voorkeur om zo mogelijk alleen left joins te gebruiken (en dan dud in jouw geval de tabellen om te draaien) om e.e.a. overzichtelijk te houden.

Er is trouwens ook nog de full (outer) join, dan krijg je alles uit beide tabellen, en als het matched komt het op dezelfde regel.
Dan krijg je dus ook het aantal users dat nog niet in een groep zit, in dit geval.

[ Voor 22% gewijzigd door Dido op 03-04-2006 16:30 ]

[OverSoft]

Thank you (allebei), weer wat geleerd

[NMe]

Was het niet zo dat left joins in MySQL beter geoptimaliseerd kunnen worden dan right joins omdat ze beter gebruik maken van indexes? Ik kan het zo snel niet meer terugvinden, maar er staat me vaag iets van bij.

[ Voor 4% gewijzigd door NMe op 03-04-2006 18:47 ]

[OverSoft]

Hmh, dat is wel aardig belangrijk in dit geval. Deze query zal rond de 30 keer per minuut uitgevoerd gaan worden en aangezien op dit moment MySQL al een van de grootste belastingen op de webserver vormt, is het wel belangrijk dat alles zo snel mogelijk is

/edit:

Ik stuit toch op een klein probleempje.
Als ik namelijk een WHERE clause toevoeg (als in "users.enabled=1") krijg ik helaas de lege groepen niet terug. Ik heb dit op het moment op een smerige manier opgelost (namelijk SUM(users.enabled) i.p.v. COUNT(users.id)), maar dit moet toch anders te doen zijn?

[ Voor 38% gewijzigd door OverSoft op 03-04-2006 19:23 ]

[Annie]

Je moet niet vergeten dat een lege groep geen users heeft en dus voor die rijen ook geen users.enabled = 1 kan gelden (je krijgt null's terug voor niet matchende rijen). De check zou dus moeten zijn WHERE (users.enabled = 1 OR users.enabled IS NULL)

[ Voor 5% gewijzigd door Annie op 03-04-2006 20:54 ]

[OverSoft]

Annie schreef op maandag 03 april 2006 @ 20:53:
Je moet niet vergeten dat een lege groep geen users heeft en dus voor die rijen ook geen users.enabled = 1 kan gelden (je krijgt null's terug voor niet matchende rijen). De check zou dus moeten zijn WHERE (users.enabled = 1 OR users.enabled IS NULL)

YES, thank you
Bedankt allemaal

[JKVA]

Je kunt de check waarschijnlijk ook al in je join conditie opnemen. Zoiets:

SQL:

SELECT COUNT(a.id) AS aantal, b.groep
FROM groep b
LEFT JOIN users a
        ON a.groep = b.id
        AND a.enabled = 1
GROUP BY groep
ORDER BY aantal DESC;

Dan zouden als het goed is alleen de users die bij een groep horen en tegelijk enabled zijn worden meegenomen in de join. Bovendien kun je zo nog iets meer optimaliseren, omdat je je index op twee kolommen kunt leggen.