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

Thank you for your attention!

Well, I knew what were the reasons for the failures; what I did not know:
how to overcome them within the limits produced by the given syntax of
joining related tables is postgreSQL.
The same query in MSAccess, for example, which has a completely different
syntax of joining tables, looked horrible, but it could do the task, it was
relatively fast, and did not require the UNION clause which seems to be the
last resort, if any, in postgreSQL.

The trouble is caused by the fact that certain data are stored at two
places: the initial data is stored in a main MainTable field, but when an
addition to that data is necessary, it is not stored directly in that field
of the MainTable, but rather a new, related record is created in the
RelatedTable, and the addition is stored there.
That is why I have to search both tables when having a search condition for
that data: search the field containing the initial data in the MainTable and
also RelatedTable if there were any additions.
For the case to be more complicated, there can be more search conditions
regarding other fields of the MainTable and other related tables, too, which
should all be AND-ed.

----- Original Message -----
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>
Sent: Monday, September 15, 2003 4:23 PM
Subject: Re: [NOVICE] Search for data in a similar field in a related table,
too

> 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.
>