Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id AD5469FB63B for ; Wed, 17 Oct 2007 05:05:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 02424-06 for ; Wed, 17 Oct 2007 05:05:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 953D59FB638 for ; Wed, 17 Oct 2007 05:05:10 -0300 (ADT) Received: from root by ciao.gmane.org with local (Exim 4.43) id 1Ii3tm-0006Yg-R7 for pgsql-sql@postgresql.org; Wed, 17 Oct 2007 08:05:02 +0000 Received: from srv.protecting.net ([212.126.218.242]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 17 Oct 2007 08:05:02 +0000 Received: from hf0217x by srv.protecting.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 17 Oct 2007 08:05:02 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: Harald Fuchs Subject: Re: Having difficulty writing a "best-fit" query.. Date: Tue, 16 Oct 2007 19:32:10 +0200 Lines: 47 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: srv.protecting.net X-Archive: encrypt User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) Cancel-Lock: sha1:vnyTbVGoF+IQ5EVHfF3gQ+2bm1M= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/80 X-Sequence-Number: 29452 In article , "Jamie Tufnell" 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.