Обсуждение: Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.

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 <josh@agliodbs.com> 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

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Hi Frederick,

On Wed, 26 Sep 2001 19:08:59 +0000 (UTC), fklauschen@yahoo.com
(Frederick Klauschen) wrote:

>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)
>

it's strange! With 7.1.3 here I don't get any parser error, but
"ERROR:  Attribute people_attributes.people_id must be GROUPed or used
in an aggregate function".  So I changed Josh's statement to

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       GROUP BY people_id) matches,      ( SELECT count(*) as attribute_count
FROMsearch_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;

and it seems to work now.

>Thank you very much,
>Frederick

HTH,Carl van Tast