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 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!