Re: Search for data in a similar field in a related table, too

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: szucs <janos(dot)szucs(at)meei(dot)hu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Search for data in a similar field in a related table, too
Date: 2003-09-15 14:23:46
Message-ID: 20030915142346.GA14494@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Wed, Sep 10, 2003 at 15:32:25 +0200,
szucs <janos(dot)szucs(at)meei(dot)hu> wrote:
> I already tried the following queries with no success:
>
> SELECT MainTable.recno FROM MainTable, RelatedTable WHERE ((MainTable.data1
> ilike 'searchpattern') and (RelatedTable.data2 ilike 'searchpattern') and
> (RelatedTable.recno=MainTable.recno));
> The above query never returned any records

The above query would only match cases where data1 and data2 both matched
their search patterns. Perhaps there aren't any records for which that
happens.

> SELECT MainTable.recno FROM MainTable, RelatedTable WHERE ((MainTable.data1
> ilike 'searchpattern') or ((RelatedTable.data2 ilike 'searchpattern') and
> (RelatedTable.recno=MainTable.recno)));
> The above query seemed to run for an indefinite time and eat up all RAM and
> CPU time

It looks like you have parenthesis in the wrong places. You want the two
ors grouped together. As it is one is grouped with the and and the other
isn't. This will result in an unconstrained join which could result
in a long running query if the two tables have a lot of entries.

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message szucs 2003-09-15 15:07:40 Re: Search for data in a similar field in a related table, too
Previous Message Wim 2003-09-15 13:05:45 Re: Error: The database does not exist on the server