Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Andy Shellam (andy-listsnetworkmail.eu)
Date: Mon Dec 22 2008 - 17:09:54 CST
Or you could wrap your entire SELECT in another query, and do an IFNULL
around the rating field to convert it to 0 (or some other value
important to you) as follows:
<... any other fields from movies table you want ...>,
IFNULL(ratings, 0) AS rating
SELECT movies.*, average(ratings.rating) AS rating FROM movies
LEFT JOIN ratings ON movies.movie_id=ratings.movie_id
GROUP BY movies.movie_id
Brent Baisley wrote:
> The ratings field would be NULL. You could also add a count in your
> query to tell how many ratings there were. If count is 0, you know
> there are no ratings.
> SELECT count(ratings.rating_id) AS rate_count, ...
> Brent Baisley
> On Mon, Dec 22, 2008 at 12:39 PM, Brian Dunning <brianbriandunning.com> wrote:
>> If I did the left join to include movies with no ratings, how would I tell
>> if it had no ratings? If I used mysql_fetch_array in PHP, would
>> $result['rating'] == 0, or '', or NULL, or what?
>> On Dec 22, 2008, at 9:29 AM, Brent Baisley wrote:
>>> The biggest problem is your join condition (and no group by). It's
>>> fine for MySQLv4, but things have changed in v5. You should start
>>> getting in the habit of moving the join filters from the WHERE clause
>>> to a specific JOIN condition. Use the WHERE clause to perform filters
>>> after the join occurs.
>>> For example:
>>> SELECT movies.* average(ratings.rating) FROM movies
>>> INNER JOIN ratings ON movies.movie_id=ratings.movie_id
>>> GROUP BY movies.movie_id
>>> Change the INNER JOIN to a LEFT JOIN if you want all movies, even
>>> those with no ratings.
>>> Brent Baisley
>>> On Mon, Dec 22, 2008 at 11:13 AM, Brian Dunning <brianbriandunning.com>
>>>> Pretend I'm Netflix and I want to return a list of found movies,
>>>> the average of related ratings for each movie. Something like this:
>>>> select movies.*, average(ratings.rating) from movies, ratings where
>>>> I'm sure that's wrong in about 10 different ways but hopefully you get
>>>> I'm trying to do. Thanks.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql