Thread: Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
From
Frederick Klauschen
Date:
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
Re: Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
From
"Josh Berkus"
Date:
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
Re: Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
From
Carl van Tast
Date:
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