PDA

View Full Version : JOINing a count?


N!ck
11 Mar 2002, 04:04
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
16 Mar 2002, 03:49
someone?

Freddie Bingham
16 Mar 2002, 04:52
something like:

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

N!ck
17 Mar 2002, 00:03
okay...thanks! :)

N!ck
17 Mar 2002, 00:46
errrr...how can i merge this with a regular query to list all the users?

N!ck
17 Mar 2002, 08:01
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
17 Mar 2002, 10:29
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
17 Mar 2002, 15:55
can you show me how to use that in a php script? lol

wooolF[RM]
17 Mar 2002, 16:00
<?
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
17 Mar 2002, 16:01
never mind! i see it now!

N!ck
17 Mar 2002, 16:13
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]
17 Mar 2002, 16:20
sry... can't help you... :(

Freddie Bingham
17 Mar 2002, 19:19
try

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

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

N!ck
17 Mar 2002, 19:54
k thanks again :)

N!ck
17 Mar 2002, 19:56
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
17 Mar 2002, 22:37
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
17 Mar 2002, 22:45
yes it appears to work just fine. thanks so much.