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

On Wed, Sep 10, 2003 at 15:32:25 +0200,
  szucs <janos.szucs@meei.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.