[SQL] Query (left join) geeft niet verwachte resultaat

[Reveller]

Ik heb 2 tabellen in mijn database:

SQL:

roles:                  users_roles:

+-----+------------+    +-----+-----+
| rid | name       |    | uid | rid |
+-----+------------+    +-----+-----+
| 1   | docent     |    | 43  | 1   |
+-----+------------+    +-----+-----+
| 2   | conrector  |    | 43  | 2   |
+-----+------------+    +-----+-----+
| 3   | congierge  |    | 46  | 1   |
+-----+------------+    +-----+-----+
                        | 47  | 1   |
                        +-----+-----+
                        | 48  | 1   |
                        +-----+-----+

Ik wil het volgende overzicht maken in mijn cms:

SQL:

+-----+------------+--------------+
| rid | name       | aantal users |
+-----+------------+--------------+
| 1   | docent     | 4            |
+-----+------------+--------------+
| 2   | conrector  | 1            |
+-----+------------+--------------+
| 3   | congierge  | 0            |
+-----+------------+--------------+

Hiervoor heb ik de volgende query gemaakt:

SQL:

SELECT r.rid, r.name, COUNT(ur.uid) AS users
FROM roles r 
  LEFT JOIN users_roles ur ON r.rid = ur.rid 
GROUP BY ur.uid
ORDER BY r.rid

Maar het resultaat hiervan is niet wat ik verwachtte. PHPMyAdmin geeft:

SQL:

+-----+------------+--------------+
| rid | name       | aantal users |
+-----+------------+--------------+
| 1   | docent     | 1            |
+-----+------------+--------------+
| 2   | conrector  | 1            |
+-----+------------+--------------+
| 2   | docent     | 2            |
+-----+------------+--------------+
| 2   | docent     | 2            |
+-----+------------+--------------+
| 2   | docent     | 1            |
+-----+------------+--------------+
| 2   | docent     | 2            |
+-----+------------+--------------+
| 2   | docent     | 2            |
+-----+------------+--------------+
| 2   | docent     | 1            |
+-----+------------+--------------+
| 2   | docent     | 1            |
+-----+------------+--------------+

// etc...

Wat doe ik verkeerd?

[Voutloos]

Je wil group by r.rid

Maar goed, je hebt een redelijk compleet verhaal in je topicstart, maar wat mist is jouw intentie, de query in woorden. Als je dan opschrijft dat je per role het aantal wil, kom je vanzelf op group by rid.

[ Voor 81% gewijzigd door Voutloos op 01-02-2007 21:33 ]

[P_de_B]

Je begrijpt GROUP BY indrdaad niet helemaal goed: klik voor uitleg

[gvanh]

Ik heb het gevoel dat je geen LEFT JOIN maar een normale JOIN moet gebruiken.
Verder inderdaad de GROUP BY op een andere kolom (zoals eerder al genoemd).

[ Voor 47% gewijzigd door gvanh op 02-02-2007 13:45 ]

[_js_]

gvanh schreef op vrijdag 02 februari 2007 @ 13:44:
Ik heb het gevoel dat je geen LEFT JOIN maar een normale JOIN moet gebruiken.
Verder inderdaad de GROUP BY op een andere kolom (zoals eerder al genoemd).

Je gevoel is verkeerd (TS wil ook de rol met 0 users terugzien).

[lier]

Dus:

code:

SELECT r.rid, r.name, COUNT(ur.uid) AS users
FROM roles r 
  LEFT JOIN users_roles ur ON r.rid = ur.rid 
GROUP BY r.rid, r.name
ORDER BY r.rid

[Voutloos]

lier schreef op vrijdag 02 februari 2007 @ 13:56:
Dus:

code:

..
GROUP BY r.rid, r.name
..

Gewoon GROUP BY r.rid dus. Een id is al uniek en de name is 1:1 afhankelijk van dat id, beetje moeilijk doen op niets af.

[lier]

Voutloos schreef op vrijdag 02 februari 2007 @ 14:10:
Gewoon GROUP BY r.rid dus. Een id is al uniek en de name is 1:1 afhankelijk van dat id, beetje moeilijk doen op niets af.

Klopt helemaal...ben iets te veel met MsSQL bezig (en daarbij is de r.name verplicht om ook in de GROUP BY op te nemen).

[Creepy]

Voutloos schreef op vrijdag 02 februari 2007 @ 14:10:
[...]

Gewoon GROUP BY r.rid dus. Een id is al uniek en de name is 1:1 afhankelijk van dat id, beetje moeilijk doen op niets af.

Tenzij je geen MySQL gebruikt. In elke andere database ben je verplicht al je niet aggregated fields in je group by op te nemen. In dit geval gaat het goed vanwege de 1:1 afhankelijkheid maar anders plukt MySQL een random waarde van 1 van de rijen er voor je uit..

[Voutloos]

Ja, ik had even over name in de select clause heen gekeken, want ik hou zelf ook niet van die mysql 'feature.'