Need help with SQL join

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hey all

I have four tables - person, movie, rating and rel_movie_rating.

The important columns in these tables are, and
movie.title, rating.value and rating.person_id,
rel_movie_rating.movie_id and

Now what I want to do is to find all the movies a certain person has
rated and their corresponding rating values. I've tried for a couple of
hours now but I always end up querying for all the ratings in the
database (over half a million), i'm guessing that I need to do some
sort of inner join here but can't quite figure out how. Any ideas on
how one should go about to solve this problem?


Re: Need help with SQL join

I solved it with

SELECT rating.value, AS, rating.person_id,
movie.title AS movie_title, movie_id AS movie_id, AS
person_id FROM rating, person, rel_movie_rating, movie WHERE
= ? AND rating.person_id = ? AND rel_movie_rating.rating_id =
AND = rel_movie_rating.movie_id

Are there maybe faster ways of doing such a join? I plan on using it in
a simple program I'm writing and would like to optimize it if possible. wrote:
Quoted text here. Click to load it

Re: Need help with SQL join wrote:
Quoted text here. Click to load it

Untested. I changed some of the names to make it easier (for me) to

SELECT f.film_title,r.rating_value FROM ratings r
LEFT JOIN films_ratings fr
ON fr.rating_id = r.rating_id
LEFT JOIN films f
ON f.film_id = fr.film_id
WHERE member_id = x

Site Timeline