homepage Welcome to WebmasterWorld Guest from 54.204.90.135
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Multiple COUNTS, Multiple tables in single query?
alphacooler




msg:1579028
 12:06 am on Jul 8, 2006 (gmt 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.

 

txbakers




msg:1579029
 3:52 am on Jul 9, 2006 (gmt 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.

syber




msg:3000571
 12:31 am on Jul 10, 2006 (gmt 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)

syber




msg:3000590
 1:04 am on Jul 10, 2006 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved