Re: Having difficulty writing a "best-fit" query..

In article <b0a4f3350710160946y4a31a227sbffbf085cdbfa487(at)mail(dot)gmail(dot)com>,
"Jamie Tufnell" <diesql(at)googlemail(dot)com> 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.