List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 14 2001 12:54am
Subject:Re: Query Even Possible in MySQL?
View as plain text  
At 1:46 AM +0100 9/14/01, Jamie Burns wrote:
>Hello,
>
>I have a query as follows:
>
>   SELECT products.*, avg(ratings.rating) AS rating
>   FROM products, ratings
>   WHERE products.ref = ratings.product_ref
>   GROUP BY products.ref
>   ORDER BY rating
>
>In one sense this query works fine - it adds a new column called 'rating'
>(which is a dynamically calculated average of ratings given to a certain
>product) to my product results. My problem is that it only works if a rating
>has been given for a product. When a new product is added, it will have no
>ratings, and so it will not be returned in any of my queries.

In other words, you want an output row even for products that are missing
in the ratings table.  You should immediately think, "aha! that's a LEFT
JOIN problem!"


Change:

FROM products, ratings WHERE products.ref = ratings.product_ref

To:

FROM products LEFT JOIN ratings ON products.ref = ratings.product_ref

And see what happens.


>
>Is there any way to assign a default value to the 'rating' column if actual
>row(s) do not exist in the ratings table? I dont really want to have to make
>a dummy rating row just to trick it into working.
>
>Am i making sense? I hope so ;o)
>
>Jamie Burns.



-- 
Paul DuBois, paul@stripped
Thread
Query Even Possible in MySQL?Jamie Burns14 Sep
  • Re: Query Even Possible in MySQL?Benjamin Pflugmann14 Sep
  • Re: Query Even Possible in MySQL?Paul DuBois14 Sep