I have to tables, users and classes. I need to show the classes count of each user with user ID and i have to show those users as well .. with no classes. how to do it ..
+2
A:
SELECT Users.id, Users.username, COUNT(*) AS classes
FROM Users
LEFT OUTER JOIN Classes ON User.fk_class_id = Classes.id
GROUP BY Users.id, Users.username
Dex
2010-06-26 00:46:52
OMG Ponies
2010-06-26 00:53:31
thanks .. .. ..
alee
2010-06-26 01:03:34
A:
I think a subselect is probably the easiest way to achieve this.
SELECT U.id, numClasses = (SELECT COUNT(1) FROM classes WHERE userID = U.id)
FROM Users U
staticbeast
2010-06-26 00:54:12
It's equivalent to the LEFT JOIN in Dex's answer, though Dex's is more liable to be efficient depending on the optimizer
OMG Ponies
2010-06-26 00:55:31
You're right, I didn't follow through the logic of the left outer join returning the multiple rows to count, rookie mistake on my part. For some reason I thought it would return users per Class, which it won't.
staticbeast
2010-06-26 01:04:09