Welcome to WebmasterWorld Guest from 54.144.110.30

Forum Moderators: open

Message Too Old, No Replies

Multiple COUNTS, Multiple tables in single query?

     
12:06 am on Jul 8, 2006 (gmt 0)

Full Member

10+ Year Member

joined:Feb 4, 2005
posts:205
votes: 0


I have 3 tables. (users), (posts), (ratings).

I would like to end up with a join that groups by userID and looks like the following:

userID ¦ count_posts_by_this_user ¦count_votes_cast_by_this_user ¦
1 -----¦-------------- 22-----------------¦--------------154--------------¦
2 -----¦-------------- etc----------------¦--------------etc--------------¦

userID is the primary key in (users)
userID a foreign key in both (posts) and (ratings)
postID and ratingID are both unique indentifiers.

The following query works fine to count posts (it is only a 2 table join):
SELECT *,
COUNT(posts.postID) as post_count
FROM posts, users
WHERE posts.userID = posts.userID
GROUP BY users.userID

But when I try to join 3 tables and add another COUNT (to count all of this user's submitted votes) using this query:

SELECT *,
COUNT(recipes.recipeID) as recipe_count, COUNT(ratings.ratingID) as ratings_count
FROM recipes, users, ratings
WHERE recipes.userID = users.userID AND
users.userID = ratings.userID
GROUP BY users.userID

all goes wrong. I get counts that are incredibly high (obviously due to some join problem).

What exactly am I missing? My guess is my join is somehow going awry.

3:52 am on July 9, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Sept 1, 2001
posts:4392
votes: 0


Hi, I wrote you, but looking at this a little closer leads me to believe that you will need multiple queries for this.

One query to get the name and the first count, then a second one to get the second count.

I don't see a way at first glance to do this with one query.

12:31 am on July 10, 2006 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 0


You have two one-to-many relationships in your joins, so each ratings.ratingID can be represented multiple times.

Try COUNT(DISTINCT ratings.ratingID)

1:04 am on July 10, 2006 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 0


I should have said that each recipes.recipeID could be listed more than once, so that COUNT(DISTINCT recipes.recipeID) would take care of it.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members