Thread: 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/

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
>

On Mon, 19 Aug 2002, Nick Fankhauser wrote:
>
> This may not be the best way, but I couldn't resist taking a shot at it...

Thanks for the response Nick. If only I knew I was going to get a
response from a block away, I would have just come down to say hi. :)

I had an "a ha" moment about this over lunch. I was making the problem
much harder than it needed to me, having assured myself I was going to
need some advanced SQL feature to solve the  problem. Some testing seems to
reveal that I can address this problem simply by joining against the
park_feature_map table N times. This way I only need to match against 1
row each of these tables, which is easy in SQL. Here's my statement I
tested with for N=2:

SELECT p.park_id, park_name   FROM parks p   JOIN park_feature_map map_4       ON (p.park_id = map_4.park_id AND
map_4.feature_id=4)  JOIN park_feature_map map_15       ON (p.park_id = map_15.park_id AND map_15.feature_id=15);
 

In this way, I'm only returned the parks that match all the features.
Thanks again for your help!
  -mark

http://mark.stosberg.com/