DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=F1cPF9AC6JzNdjzGIwoHJ64H+qt6d5VuCBlmz+BswAjkYwAU21aJIf+dJJfzd3ndetNJV4m9DIbKIP9U3UNUCh62ru/ONWVKKRI7o/Q9+pBIIS+jDuU9v4S2l/vWi7TQ5YwQKL2lxQ3DjFV9reDw9y3KAsq+cH8bkrnuxME0ewg=
Message-ID: <b0a4f3350710160946y4a31a227sbffbf085cdbfa487@mail.gmail.com>
Date: Tue, 16 Oct 2007 17:46:33 +0100
From: "Jamie Tufnell" <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Having difficulty writing a "best-fit" query..
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3229_4747650.1192553193879"

------=_Part_3229_4747650.1192553193879
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_3229_4747650.1192553193879
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi list,<br><br>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 &quot;appropriateness&quot; of the relations within each movie.&nbsp; <br><br>For example:
<br><br>movie_id, genre_id, relevance (i&#39;ve used movie/genre titles for clarity here, but in reality they&#39;re id&#39;s)<br>--------------------------------------------<br>beverly hills cop, action, 2<br>beverly hills cop, comedy, 1
<br>the heartbreak kid, comedy, 2<br>the heartbreak kid, romance, 1<br><br>The above would mean, to my application:<br>&quot;Beverly Hills Cop is both an Action and a Comedy movie but primarily an Action movie.&quot;<br>
&quot;The Heartbreak Kid is both a Comedy and a Romance movie but primarily a Comedy movie.&quot;<br>
<br>First of all, if there&#39;s a better way to model this kind of ranking/ordering of many-to-many relationships, please let me know.<br><br>Now, to my problem..<br><br>I&#39;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).&nbsp; In other words, the best fit.
<br><br>I&#39;ve had a few goes at writing this query but I can&#39;t seem to get it right.. The theory in my mind is to:<br><br>1. filter the link table down to rows that fit the subset of categories (easy)<br><br>2. filter the link table further to keep only the max(relevance) for each movie_id .. this is where i&#39;m having trouble.
<br><br>If someone can shed some light on this for me, I&#39;d really appreciate it.<br><br>Thanks for your time,<br>Jamie<br>

------=_Part_3229_4747650.1192553193879--