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 <br...@briandunning.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 <br...@briandunning.com> >> wrote: >>> >>> Pretend I'm Netflix and I want to return a list of found movies, >>> including >>> the average of related ratings for each movie. Something like this: >>> >>> select movies.*, average(ratings.rating) from movies, ratings where >>> movies.movie_id=ratings.movie_id >>> >>> I'm sure that's wrong in about 10 different ways but hopefully you get >>> what >>> I'm trying to do. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
