Hi.
Just use a LEFT JOIN and you will get a "default of NULL" whenever
there is no entry in the rating table for a product_ref.
Bye,
Benjamin.
On Fri, Sep 14, 2001 at 01:46:01AM +0100, [EMAIL PROTECTED] 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.
>
> 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)
>
---------------------------------------------------------------------
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
