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, [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
