At 1:46 AM +0100 9/14/01, Jamie Burns wrote:
>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
FROM products, ratings WHERE products.ref = ratings.product_ref
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)
Paul DuBois, paul@stripped