JOINing a count?
 

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!

someone?

something like:

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

okay...thanks! :)

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

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

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

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

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

never mind! i see it now!