pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
3+ messages / 3 participants
[nested] [flat]
* Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
@ 2001-09-26 18:45 Frederick Klauschen <fklauschen@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: Frederick Klauschen @ 2001-09-26 18:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
@ 2001-09-26 19:09 Josh Berkus <josh@agliodbs.com>
parent: Frederick Klauschen <fklauschen@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2001-09-26 19:09 UTC (permalink / raw)
To: Frederick Klauschen <fklauschen@yahoo.com>; Josh Berkus <josh@agliodbs.com>; pgsql-sql
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 josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
@ 2001-09-26 22:21 Carl van Tast <vanTast@Pivot.at>
parent: Frederick Klauschen <fklauschen@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Carl van Tast @ 2001-09-26 22:21 UTC (permalink / raw)
To: pgsql-sql
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
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;
and it seems to work now.
>Thank you very much,
>Frederick
HTH,
Carl van Tast
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-09-26 22:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-26 18:45 Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution. Frederick Klauschen <fklauschen@yahoo.com>
2001-09-26 19:09 ` Josh Berkus <josh@agliodbs.com>
2001-09-26 22:21 ` Carl van Tast <vanTast@Pivot.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox