From: Brian Dunning (brianbriandunning.com)
Date: Mon Dec 22 2008 - 11:39:08 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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
> > 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=brenttechgmail.com
>>
>>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]