JOINing a count?

[N!ck]

my problem now is that it only lists MY name!

the query:

SELECT COUNT(id) AS total, user.userid, user.username FROM user LEFT JOIN userphotos USING (userid) GROUP BY userphotos.userid ORDER BY user.usergroupid DESC, user.username LIMIT $offset, 15

($offset is set via a query string, or else it equals zero)

and also, i did COUNT(id) because id exists in the userphotos table and not in the user table.

[wooolF[RM]]

]sry... can't help you...

[Freddie Bingham]

try

GROUP BY user.userid instead of GROUP BY userphotos.userid

using COUNT(*) is faster than using COUNT(id)

[N!ck]

k thanks again

[N!ck]

okay, better...but now it outputs "1" instead of "0" (nobody has posted any pics yet, but they all have 1's next their names)

[Freddie Bingham]

Try this

SELECT user.userid, user.username, count(userphotos.userid) AS total
FROM user
LEFT JOIN userphoto ON (userphoto.userid=user.userid)
GROUP BY user.userid
ORDER BY user.usergroupid DESC, user.username
LIMIT $offset, 15

This would give you then number of attachments each user has as I verified it. Since the above follows the same logic, it has to work.:

SELECT user.userid, user.username, count(attachment.userid) as attachmentcount
FROM user
LEFT JOIN attachment ON (user.userid=attachment.userid)
GROUP BY user.userid

[N!ck]

yes it appears to work just fine. thanks so much.