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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
