pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query 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