List:General Discussion« Previous MessageNext Message »
From:ktmpyah Date:August 15 2005 9:30pm
Subject:multiple JOINs / GROUP BY (beginnrer) question
View as plain text  
Hello.
I am trying to obtain a list of products and related information from
three tables using JOINs and GROUP BY.

my tables look like this:
products:
  id: int
  name varchar
ratings:
  user_id int
  product_id int
  rating int
wishlists:
  user_id int
  product_id int
  stars int

And I want the result to look like this:
product_id | product_name | my_rating | avg_rating | total_ratings |
my_stars | avg_stars | total_stars | total_star_users

Tables 'ratings' and 'wishlists' do not necessarily contain entries
for each user_id; also a user can rate a product, but not have it in
his wishlist and vice-versa (this is the cause of my problem).

My question is if it is possible to do this in mysql by using a single
query, without using temporary tables.

I tried to use something like this:

SELECT
 p.id AS product_id, p.name AS product_name,
 r1.rating AS my_rating,
 AVG(r2.rating) AS avg_rating, COUNT(DISTINCT(r2.user_id)) AS total_ratings,
 w1.stars AS my_stars,
 AVG(w2.stars) AS avg_stars, SUM(w2.stars) AS total_stars,
COUNT(DISTINCT(w2.stars)) AS total_star_users
FROM products p
 LEFT JOIN ratings r1 ON r1.product_id=p.id AND r1.user_id=<current_user_id>
 LEFT JOIN ratings r2 ON r2.product_id=p.id
 LEFT JOIN wishlists w1 ON w1.product_id=p.id AND w1.user_id=<current_user_id>
 LEFT JOIN wishlists w2 ON w2.product_id=p.id
GROUP BY product_id
ORDER BY product_name;

The problem is the field 'total_stars' which shows incorrectly, as it
SUMs more data than necessary in some cases.

Can anyone help?
Thanks.
Thread
multiple JOINs / GROUP BY (beginnrer) questionktmpyah15 Aug