Received: from web10108.mail.yahoo.com (web10108.mail.yahoo.com [216.136.130.58]) by postgresql.org (8.11.3/8.11.4) with SMTP id f8QIjph54905 for ; Wed, 26 Sep 2001 14:45:51 -0400 (EDT) (envelope-from fklauschen@yahoo.com) Message-ID: <20010926184545.20714.qmail@web10108.mail.yahoo.com> Received: from [128.231.231.124] by web10108.mail.yahoo.com via HTTP; Wed, 26 Sep 2001 11:45:45 PDT Date: Wed, 26 Sep 2001 11:45:45 -0700 (PDT) From: Frederick Klauschen Subject: Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution. To: Josh Berkus , pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200109/229 X-Sequence-Number: 4911 Hi Josh, I am really sorry to bother you again and I promise to buy a book after this problem is solved and stop asking these "beginners'" questions. But the last example-Query you sent me does not work, it produces: ERROR: parser: parse error at or near "(" and I am sure, I entered it correctly. (I also tried on PostgreSQL Versions 7.0.3 and 7.1.3) Thank you very much, Frederick --- Josh Berkus wrote: > Frederick, > > > "Mary Stuart" correctly. But such a query also > > seems to get results that contain only one > > of the search_attributes. > > e.g. a 32 "Peter Smith" who e.g. just has an entry > > 24 32 "hair" "brown" (and no mice hobby) is also > > found. > > I need to get only results that match the search > > completely. > > I would be happy if you could help me again. > > Thanks, Frederick > > Oops. You are quite correct. Unfortunately, the > query that you need is > somewhat more complicated: > SELECT people.people_id, people.name, > people.address, > people_attributes.attribute_name, > people_attributes.attribute_value > FROM people, people_attributes, > ( SELECT people_id, count(*) as match_count > FROM people_attributes, search_attributes > WHERE search_id = 31 > AND people_attributes.attribute_name = > search_attributes.attribute_name > AND people_attributes.attribute_value ~* > search_attributes.attribute_value ) > matches, > ( SELECT count(*) as attribute_count > FROM search_attributes > WHERE search_id = 31 ) searched > WHERE people.people_id = people_attributes.people_id > AND people.people_id = matches.people_id > AND matches.match_count = > searched.attribute_count; > > This structure will also allow you to search for, > say, 4 out of 5 items > by changing the last line to: > AND matches.match_count >= > (searched.attribute_count - 1); > > Also, if you re-arrange the query slightly, you can > turn it into a view. > The trick is to have the search_id as an output > column rather than a > WHERE clause item in the sub-selects. > > Have fun! > > -Josh > > ______AGLIO DATABASE > SOLUTIONS___________________________ > Josh Berkus > Complete information technology > josh@agliodbs.com > and data management solutions (415) > 565-7293 > for law firms, small businesses fax > 621-2533 > and non-profit organizations. San Francisco > > > > > > > __________________________________________________ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com