Page 1 of 1

Counting 2 levels deep

Posted: Tue May 06, 2003 4:39 am
by []InTeR[]

Code: Select all

SELECT usergroup. * , count( usergroup_type.type_id ) num, count( user.id ) numdeb
FROM usergroup
LEFT JOIN usergroup_type ON usergroup.id = usergroup_type.usergroup_id
LEFT JOIN user ON user.type_id = usergroup_type.type_id
GROUP BY usergroup.id LIMIT 0, 50
What i now get is not correct, the 'num' is okay but the 'numdeb' not.

I want to count the number of user in a usergroup.
But this there is a link-table usergroup_type (links usergroup.id to user.type_id, many on many).

Does anyone know how you can count the number of user in a usergroup?

MySQL Version: 3.23.41