Reply-To: <nickf@ontko.com>
From: "Nick Fankhauser" <nickf@ontko.com>
To: "Mark Stosberg" <mark@summersault.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: need assistance with multi-row matching expression
Date: Mon, 19 Aug 2002 11:52:43 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGMEJIFJAA.nickf@ontko.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <Pine.BSF.4.44.0208191013320.75440-100000@nollie.summersault.com>

Mark-

This may not be the best way, but I couldn't resist taking a shot at it...

If I understand correctly, your user selects 0-n features, so you are
essentially querying against park_feature_map with your known parameters
being the number of features and a feature_id list.

suppose your park_feature_map was created like this:

create table pfm (pid integer, fid integer);

If a user wants all of the parks with features 1,2 & 3 then the feature list
is (1,2,3) and the number of features is 3. I think this select would work:

select case when count(pid) = 3 then pid end from pfm where fid in (1,2,3)
group by pid

or to illustrate the query better, you could use this:

select pid, case when count(pid) = 3 then 'yes' else 'no' end from pfm where
fid in (1,2,3) group by pid;

It seems like you might also want to rank matches, so you could also do:

select pid, count(pid) from pfm where fid in (1,2,3) group by pid order by
count(pid) desc;

The last one doesn't pinpoint matches, but might end up making a better user
interface. You could combine the two to only list parks with at least N-1
matches like so:

select case when count(pid) > (3-1) then pid end from pfm where fid in
(1,2,3) group by pid order by count(pid) desc;

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Mark Stosberg
> Sent: Monday, August 19, 2002 10:21 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] need assistance with multi-row matching expression
>
>
>
> Hello,
>
> I'm using Postgres 7.1 and have been enjoying the upgraded "sub-select"
> support in this release versus older versions.
>
> At the moment, I'm stuck with a SQL issue that I haven't run into
> before.
>
> I need to select the data for all the "parks" that match some search
> criteria. The parks are stored in a "parks" table with a park_id as a
> primary key.
>
> Part of the search includes the logic of "match parks that include all
> these features". The "features" are stored in their own table, and are
> related to the parks table with a park_feature_map table, which contains
> a park_id column and a feature_id column.
>
> A user can use 0 to N to features, and each park might have 0 to N
> entries in the park_feature_map table.
>
> Where I'm stuck is that I'm used to putting together SQL statements to
> match a given row. This is different-- to create a successful match for
> a park_id, I need to check to match against N rows, where N is the
> number of feature_ids provided.
>
> How do I do that? Can I do it in one query?
>
> Thanks!
>
>   -mark
>
> http://mark.stosberg.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>