In article <[EMAIL PROTECTED]>,
"Jamie Tufnell" <[EMAIL PROTECTED]> writes:
> Hi list,
> I have a many-to-many relationship between movies and genres and, in the link
> table I have a third field called which orders the "appropriateness" of the
> relations within each movie.
> For example:
> movie_id, genre_id, relevance (i've used movie/genre titles for clarity here,
> but in reality they're id's)
> --------------------------------------------
> beverly hills cop, action, 2
> beverly hills cop, comedy, 1
> the heartbreak kid, comedy, 2
> the heartbreak kid, romance, 1
> The above would mean, to my application:
> "Beverly Hills Cop is both an Action and a Comedy movie but primarily an
> Action
> movie."
> "The Heartbreak Kid is both a Comedy and a Romance movie but primarily a
> Comedy
> movie."
> First of all, if there's a better way to model this kind of ranking/ordering
> of
> many-to-many relationships, please let me know.
This looks fine to me.
> Now, to my problem..
> I'm taking a subset of all my genres, and I want to get ONE row for each movie
> in the subset alongside its most appropriate genre (whichever has the highest
> relevance). In other words, the best fit.
You could use something like that:
SELECT m.name, g.name, mg.relevance
FROM movies m
JOIN mg ON mg.movie_id = m.id
JOIN genres g ON g.id = mg.genre_id
LEFT JOIN mg mg1 ON mg1.movie_id = mg.movie_id
AND mg1.relevance > mg.relevance
WHERE mg1.movie_id IS NULL
This means that there must not be an link table entry for the same
movie with a higher relevance.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
