Welcome to WebmasterWorld Guest from 23.20.238.193

Forum Moderators: open

Message Too Old, No Replies

Multiple COUNTS, Multiple tables in single query?

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

10+ Year Member



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 Jul 9, 2006 (gmt 0)

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



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 Jul 10, 2006 (gmt 0)

10+ Year Member



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 Jul 10, 2006 (gmt 0)

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month