From: Harald Fuchs <hf0217x@protecting.net>
To: pgsql-sql@postgresql.org
Subject: Re: Having difficulty writing a "best-fit" query..
Date: Tue, 16 Oct 2007 19:32:10 +0200
Message-ID: <pulka30xut.fsf@srv.protecting.net> (raw)
References: <b0a4f3350710160946y4a31a227sbffbf085cdbfa487@mail.gmail.com>
In article <b0a4f3350710160946y4a31a227sbffbf085cdbfa487@mail.gmail.com>,
"Jamie Tufnell" <diesql@googlemail.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hf0217x@protecting.net
Subject: Re: Having difficulty writing a "best-fit" query..
In-Reply-To: <pulka30xut.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox