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 [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Mark Stosberg > Sent: Monday, August 19, 2002 10:21 AM > To: [EMAIL PROTECTED] > 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 > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
