Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Brian Dunning (brianbriandunning.com)
Date: Mon Dec 22 2008 - 11:39:08 CST
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,
>> 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 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