JOINing a count?

[N!ck]

How do I join a count from another table? I want to list all users and to see how many photos (stored in a separate table) a particular user has submitted. Can someone show me the MySQL syntax? Thanks!

[N!ck]

someone?

[Freddie Bingham]

something like:

SELECT count(*) as total
FROM user, photos
WHERE user.userid=photo.userid
GROUP BY photo.userid

[N!ck]

okay...thanks! :)

[N!ck]

errrr...how can i merge this with a regular query to list all the users?

[N!ck]

yeah so it freddie or someone could show me an example of how to list all members and for each one show how many photos they have in a table called "userphotos", i would very much appreciate it :D thanks

[Freddie Bingham]

This would only list those with at least one photo

SELECT count(*) as total , user.username
FROM user, photos
WHERE user.userid=photo.userid
GROUP BY photo.userid

or this would show all users, putting 0 for those with none.

SELECT count(*) as total , user.username
FROM user
LEFT JOIN photos USING (userid)
GROUP BY photo.userid

[N!ck]

can you show me how to use that in a php script? lol

[wooolF[RM]]

]
<?
SELECT count(*) as total , user.username
FROM user, photos
WHERE user.userid=photo.userid
GROUP BY photo.userid
?>



<?
SELECT count(*) as total , user.username
FROM user
LEFT JOIN photos USING (userid)
GROUP BY photo.userid
?>


PS: I'm not 100% sure about it...

[N!ck]

never mind! i see it now!

[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 thisSELECT 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.