Need help with SQL join

Hey all

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

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

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?

Thanks!

Re: Need help with SQL join

I solved it with

SELECT rating.value, rating.id AS rating.id, rating.person_id,
movie.title AS movie_title, movie_id AS movie_id, person.id AS
person_id FROM rating, person, rel_movie_rating, movie WHERE person.id
= ? AND rating.person_id = ? AND rel_movie_rating.rating_id = rating.id
AND movie.id = 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.


deepstar@gmail.com wrote:
> Hey all
>
> I have four tables - person, movie, rating and rel_movie_rating.
>
> The important columns in these tables are person.id, movie.id and
> movie.title, rating.value and rating.person_id,
> rel_movie_rating.movie_id and rel_movie_rating.person.id.
>
> 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?
>
> Thanks!

Re: Need help with SQL join

nimaonsafari@gmail.com wrote:
> I solved it with
>
> SELECT rating.value, rating.id AS rating.id, rating.person_id,
> movie.title AS movie_title, movie_id AS movie_id, person.id AS
> person_id FROM rating, person, rel_movie_rating, movie WHERE person.id
> = ? AND rating.person_id = ? AND rel_movie_rating.rating_id = rating.id
> AND movie.id = 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.
>
>
> deepstar@gmail.com wrote:
> > Hey all
> >
> > I have four tables - person, movie, rating and rel_movie_rating.
> >
> > The important columns in these tables are person.id, movie.id and
> > movie.title, rating.value and rating.person_id,
> > rel_movie_rating.movie_id and rel_movie_rating.person.id.
> >
> > 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?
> >
> > Thanks!

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

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