Re: Query does not work: parse ERROR, Re: [SQL] How to enter lists into database: Problems with solution.

2001-09-26 Thread Josh Berkus 

Frederick,

 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.

Nah, this one wasn't a beginners question.  Intermediate, maybe.

 But the last example-Query you sent me does not
 work, it produces:
 
 ERROR: parser: parse error at or near (

Hmmm... I can't see anything wrong with the query.  Unfortunately, I
have not set up this test database myself, so I can't actually run it.

Maybe someone else can spot the syntax error?  I've looked it over again
and there's nothing missing.  Is it possible, Frederick, that the comma
after matches or people_attributes got cut off?  

-Josh

__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Query does not work: parse ERROR, Re: [SQL] How to enter lists into database: Problems with solution.

2001-09-26 Thread Frederick Klauschen 

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 [EMAIL PROTECTED] 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 
 [EMAIL PROTECTED]
and data management solutions   (415)
 565-7293
   for law firms, small businessesfax
 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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org