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. 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. I've had a few goes at writing this query but I can't seem to get it right.. The theory in my mind is to: 1. filter the link table down to rows that fit the subset of categories (easy) 2. filter the link table further to keep only the max(relevance) for each movie_id .. this is where i'm having trouble. If someone can shed some light on this for me, I'd really appreciate it. Thanks for your time, Jamie
