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=brentt...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
